MySQL replace - manipulate portion of string

Question

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?

I have realized some links end in ....=22 (that's two digits). I think I will end up using a php script to jump the numbers ... :( — Mg Gm
– Mg Gm, Commented Feb 25, 2016 at 13:08

sagi · Accepted Answer · 2016-02-25 11:58:04Z

1

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=%'

edited Feb 25, 2016 at 11:58

answered Feb 25, 2016 at 11:51

sagi

40.6k7 gold badges66 silver badges87 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:15:41Z

0

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.

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Feb 25, 2016 at 12:04

Zamrony P. Juhara

5,2822 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

MySQL replace - manipulate portion of string

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