CODEWITHSUNDEEP
mysqlsqlregex
user6792802
user6792802

Reputation:

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?

Edit: If there's another comma it should get ignored.

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

Upvotes: 2

Views: 81

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

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]+$'

Upvotes: 1

Related Questions