David David - 1 year ago 88
MySQL Question

MySQL; find and replace

In my MySQL database I've some strings like this:

I'm something, Infos S. 12

The pattern I want to search for is:
, Infos S.
, than a number (only digits) and than string end. It's case-insensitive.

How can I search for it and remove it?

I have this
I'm something, Infos S. 12
and I want
I'm something

This is what I have so far:

UPDATE my_table SET title_col = REPLACE(title_col, SUBSTRING(title_col, LOCATE(', Infos S. ', title_col), LENGTH(title_col) - LOCATE(')', REVERSE(title_col)) - LOCATE(', Infos S. ', title_col) + 2), '') WHERE title_col LIKE '%(%)%';

How to do the rest?

If there's another comma it should get ignored.

I'm, something, Infos S. 12
(note the comma after
) should get
I'm, something

vkp vkp
Answer Source

You can use regexp to check if the column has the specified pattern and then use substring to update the string with substring upto the ,.

UPDATE my_table 
SET title_col = SUBSTRING(title_col,1,locate(', Infos',title_col)-1) 
WHERE title_col regexp ', Infos S\\. [0-9]+$'

regexp match is case-insensitive by default. If you want to make it case-sensitive, use regexp binary.

where title_col regexp binary ', Infos S\\. [0-9]+$'

Note: The solution assumes there is no , before , Infos S. in the string.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download