0

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?

Mg Gm
  • 151
  • 1
  • 11

2 Answers2

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=%'
sagi
  • 40,026
  • 6
  • 59
  • 84
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.

Community
  • 1
  • 1
Zamrony P. Juhara
  • 5,222
  • 2
  • 24
  • 40