Mysql replace string when needed query

Question

I have a table called: k01cv_sh404sef_urls Inside this table we have the following rows:

id, cpt, rank, oldurl, newurl and dateadd

The row "oldurl" contains entry's such as:

English/network
English/hardware-and-software
corporate-social-responsibility

I'd like to have a query that removes the English/ part from all the items in the "oldurl" row so that the english/network would be just network, English/hardware-and-software would be hardware-and-software, etc.

The result would be that the row "oldurl" contains entry's such as:

network
hardware-and-software
corporate-social-responsibility

Roman Starkov · Accepted Answer · 2015-04-20 23:16:48Z

1

Can you get away with something as simple as this?

UPDATE table SET oldurl = REPLACE(oldurl, 'English/', '');

(please test using a SELECT before running it on the real data!)

answered Apr 20, 2015 at 23:16

Roman Starkov

62.1k41 gold badges262 silver badges331 bronze badges

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

Comments

eggyal · Accepted Answer · 2015-04-20 23:33:12Z

1

UPDATE k01cv_sh404sef_urls
SET    oldurl = SUBSTRING(oldurl FROM 9)
WHERE  oldurl LIKE 'English/%'

This will not just read/attempt to update only matching records (which will yield a particular performance benefit if your table is large but has an index in which oldurl is the leftmost column), but will also only strip the 'English/' that appears at the very start of the string.

answered Apr 20, 2015 at 23:33

eggyal

127k18 gold badges220 silver badges244 bronze badges

1 Comment

gijs007 Over a year ago

Thanks, that works perfectly. I also have some url's like nl/nederlands/hardware-en-software Where I want to remove the /nederlands/ part. How can I accomplish this?

Collectives™ on Stack Overflow

Mysql replace string when needed query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related