How to do MySQL + Substring? + replace?

Question

I am not very good with SQL and would like to be able to become better.
I am having some trouble trying to preform a certain table manipulation.
I would like to be able to select the substring out of the ProgUID column below

something like...

SUBSTRING(table.ProgUID,3,12);

which would give me CAMVE-9701 for the ProgUID P-CAMVE-9701-1 (removing the P- from the beginning and the -1 from the end), and then insert the substring into that rows UID.

I assume this should be fairly easy, and I have been trying to figure it out but havent had much luck.

If there is a better approach please let me know!

Thanks in advance for your thoughts / help!

enter image description here

John Woo · Accepted Answer · 2012-11-02 14:47:53Z

5

use UPDATE statement

UPDATE tableName
   SET UID = SUBSTRING(ProgUID,3,12)

See SQLFiddle Demo

edited Nov 2, 2012 at 14:47

answered Nov 2, 2012 at 14:41

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ethorn10 · Accepted Answer · 2012-11-02 14:43:19Z

2

If the portion you want is always 12 characters, then

UPDATE table
SET UID = SUBSTRING(ProgUID, 3, 12)

otherwise

UPDATE table
SET UID = SUBSTRING(ProgUID, 3, LENGTH(ProgUID)-2)

answered Nov 2, 2012 at 14:43

ethorn10

1,8991 gold badge18 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to do MySQL + Substring? + replace?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related