CODEWITHSUNDEEP
mysql
Hunnenkoenig
Hunnenkoenig

Reputation: 199

how to delete/replace parts of a string in mysql using wildcards?

I have thousands of strings in a MySQL table like

some-text-for-read/id123456
some-other-text-to-read-too/id456789

I want to make them

/id123456
/id456789

How can i do it using wildcards for the text part? I tried everything I found like "REPLACE %id%" kind of queries, but nothing worked, because I didn't do it right probably.

Upvotes: 3

Views: 1194

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270607

Using a combination of the MySQL string functions LOCATE() and SUBSTR(), you use LOCATE() to find the position in the string of the first / character, and SUBSTR() to return the substring beginning with that position in the original string.

You can place all of it into an UPDATE statement:

UPDATE your_table
SET your_column = SUBSTR(your_column, LOCATE('/', your_column))
-- Only on rows where the / is present, if there are non-matching rows
-- LOCATE() returns 0 when the string isn't found
WHERE LOCATE('/', your_column) > 0

Example:

> SELECT SUBSTR('some-other-text-to-read-too/id456789', LOCATE('/', 'some-other-text-to-read-too/id456789')) AS val;
+-----------+
| val       |
+-----------+
| /id456789 |
+-----------+

And the reason you need the WHERE clause -- everything will be deleted if there is no / in the row:

/* This has no '/' */
> SELECT SUBSTR('some-other-text-to-read-tooid456789', LOCATE('/', 'some-other-text-to-read-tooid456789')) AS val;
+-----+
| val |
+-----+
|     |
+-----+

Upvotes: 4

Jun Rikson
Jun Rikson

Reputation: 1884

Try this :

UPDATE your_table set your_field = CONCAT('/', SUBSTRING_INDEX(your_field, '/', -1))

Upvotes: 1

Related Questions