CODEWITHSUNDEEP
mysql
Mg Gm
Mg Gm

Reputation: 151

MySQL replace - manipulate portion of string

Inside a mysql table have a column containing relative urls and other urls and I would like to jump the last digit by 10000 (for the relative urls)

...
index.php?option=com_content&view=article&id=220
index.php?option=com_content&view=article&id=221
index.php?option=com_content&view=article&id=222
http://somerandomurl.com
index.php?option=com_content&view=article&id=227
http://anotherrandomurl.com
...

I know how to replace fixed portions of strings but I still don't know how to programmatically change parts of the string so that it results in this pattern

...
index.php?option=com_content&view=article&id=10220
index.php?option=com_content&view=article&id=10221
index.php?option=com_content&view=article&id=10222
http://somerandomurl.com
index.php?option=com_content&view=article&id=10227
http://anotherrandomurl.com
...


UPDATE url_table
SET url_field = REPLACE ?????????
WHERE url_field LIKE '%index.php?option=com_content&view=article&id=%'

Is there a straightforward way to achieve this operation?

Upvotes: 0

Views: 40

Answers (2)

Zamrony P. Juhara
Zamrony P. Juhara

Reputation: 5262

If it always 3 digit from right that need to be inserted then @sagi's answer is correct. But if that is not the case, then you need regular expression. MariaDB since version 10 has REGEXP_REPLACE function for replacing string with regular expression. This discussion might help if you do not use MariaDB.

Upvotes: 0

sagi
sagi

Reputation: 40491

If the number is always 3 digits, then you can use this:

UPDATE url_table
SET url_field = replace(url_field,right(url_field,3),right(url_field,3)+10000)
WHERE url_field = LIKE '%index.php?option=com_content&view=article&id=%'

I'm not sure how mysql deals with a number in string type column so if it doesn't accept it:

UPDATE url_table
SET url_field = replace(url_field,right(url_field,3),
                       cast(right(url_field,3) to number) +10000 to string)
WHERE url_field = LIKE '%index.php?option=com_content&view=article&id=%'

Upvotes: 1

Related Questions