How to replace a regex pattern in MySQL

问题:

I have a table called myTable which has a column called col1. This column contains data in this format: (1 or 2 digits)(hyphen)(8 digits).

I want to replace all the data in this column and replace everything before hyphen with 4, so this is an example:

--------------------------------
|  old values   |  New Values  |
--------------------------------
| 1-654283568  =>  4-654283568 |
| 2-467862833  =>  4-467862833 |
| 8-478934293  =>  4-478934293 |
| 12-573789475 =>  4-573789475 |
| 16-574738575 =>  4-574738575 |
--------------------------------

I am using MySQL 5.7.19, I believe REGEXP_REPLACE is available in MySQL Version 8+... not sure how this can be achieved?

回答1:

You don't need regex; you can use SUBSTRING_INDEX to extract everything after the hyphen and concatenate 4- to that:

UPDATE myTable
SET col1 = CONCAT('4-', SUBSTRING_INDEX(col1, '-', -1))

Demo on dbfiddle

This will work regardless of the number of characters after the hyphen.

回答2:

Looking to your pattern seem you could avoid regexp

update myTable  
set col1  = concat('4-', right(col1,8))

or

update myTable  
set col1  = concat('4', right(col1,9))

回答3:

Try this:

UPDATE testing SET val=REPLACE(val,SUBSTRING(val,1,LOCATE('-',val)),'4-');

Fiddle here :https://www.db-fiddle.com/f/4mU5ctLh8NB9iKSKZF9Ue2/2

Using LOCATE to find '-' position then use SUBSTRING to get only the front part of the '-'.

回答4:

SELECT CONCAT( @new_prefix, SUBSTRING(old_value FROM LOCATE('-', old_value)) ) AS new_value

---

UPDATE sourcetable
SET fieldname = CONCAT( '4', SUBSTRING(fieldname FROM LOCATE('-', fieldname)) )
WHERE LOCATE('-', fieldname)
/* AND another conditions */