CODEWITHSUNDEEP
mysqlstringreplace
gijs007
gijs007

Reputation: 233

Mysql replace string when needed query

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

Upvotes: 0

Views: 23

Answers (2)

eggyal
eggyal

Reputation: 125835

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.

Upvotes: 1

Roman Starkov
Roman Starkov

Reputation: 61382

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!)

Upvotes: 1

Related Questions