Nrc Nrc - 1 year ago 108
MySQL Question

SQL replace any number

I am in MySql. I have the table named blog and the column photo. I want to make some changes for instance. All the values in that column have that schema:
For instance: 1/marketing.svg

I want to delete the number/
In the example, I want to have: text.svg.
But I want it work with any number, one, two or three digits.

This is what I tried

SET photo = REPLACE(photo, '%/', '')

vkp vkp
Answer Source

If the string format is consistent, i.e. a number followed by a / and then the text, you can use a combination of substring and locate to update the column.

UPDATE blog 
SET photo = SUBSTRING(photo, LOCATE('/',photo)+1)
WHERE LOCATE('/',photo) > 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download