SQL - How do I replace/modify strings in multiple rows?

Question

I have a lot of rows in database where column "info" is ie. "Size, inch: 12x13 Material: paper Amount: 100pcs". Now I need to find/select all rows that has this string part "Size, inch:" and replace/fix those rows' colums to ie. "Size: 12x13 inch  Material: paper Amount: 100pcs ".

How in the world I write a sql statement for this? Do I need some magic regexp for sql? How do I replace and modify parts of a string in multiple rows?

EDIT: The numbers can be anything (ie. 12x13 or 44x55 or 77x88x99 etc.) So there would have to be some kind of wildcard for the numbers, perhaps?

I need to change "Size, inch: 'anynumbershere' 'anythingafter the numbers'" to "Size: 'anynumbershere' inch 'anythingafter'".

Check for mysql replace function.

Abhik Chakraborty
– Abhik Chakraborty

2015-07-22 10:29:52 +00:00
Commented Jul 22, 2015 at 10:29 — Abhik Chakraborty
– Abhik Chakraborty, Commented Jul 22, 2015 at 10:29

apomene · Accepted Answer · 2015-07-22 10:33:48Z

1

update mytable set info=REPLACE ( info , 'Size, inch: 12x13 ', 'Size: 12x13 inch' )
where info like '%inch:12X13%'

answered Jul 22, 2015 at 10:33

apomene

14.4k10 gold badges51 silver badges76 bronze badges

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

1 Comment

H3ll1n Over a year ago

My numbers 12x13 are different in every row. I'm not sure this works. In some rows there can be ie. "Size, inch: 45x88x99 and so on..."

Gordon Linoff · Accepted Answer · 2015-07-22 10:55:56Z

0

Perhaps this will work:

update table t
    info = replace(replace(info, 'Size inch:', 'Size:'), '<br>Material', ' inch<br>Material')
    where info like 'Size inch:%<br>Material%';

Note: this assumes that 'Size inch' and ' Material' only appear once in each string.

answered Jul 22, 2015 at 10:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

H3ll1n Over a year ago

The "material" after isn't always there. This is my point why this is painful. I need to change "Size, inch: <anynumbershere><anythingafter the numbers>" to "Size: <anynumbershere> inch<anythingafter>".

Gordon Linoff Over a year ago

@H3ll1n . . . We can only answer the question that is asked. If you have a different question, you should ask it as a different question.

H3ll1n Over a year ago

My bad. I got too specific.

Collectives™ on Stack Overflow

SQL - How do I replace/modify strings in multiple rows?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related