Query for converting a column of numbers matching a regex in MySQL

Question

I have a column which contains string values of the format e.g AB12345678 - two letters followed by 8 numeric digits.

I need to write a MySQL query to insert a 5 before each set of 4 digits thus AB12345678 becomes AB5123455678.

I'm close, the following works for one value in the column, but not for multiple values - all values are written with the same first value encountered it seems.

SELECT @twoeight := os_grid_ref FROM projects WHERE os_grid_ref REGEXP '[A-Z]{2}[0-9]{4}[0-9]{4}';
SELECT @prefix1 := SUBSTR(@twoeight,1,2);
SELECT @part1 := SUBSTR(@twoeight,3,4);
SELECT @part2 := SUBSTR(@twoeight,7,4);
UPDATE projects SET os_grid_ref=CONCAT(@prefix1,'5',@part1,'5',@part2) WHERE os_grid_ref REGEXP '[A-Z]{2}[0-9]{4}[0-9]{4}';

What do I need to adjust so that the query can modify each value in the column individually.

Thank you.

Answer 1

If you need to change those things in place:

UPDATE projects
 SET os_grid_ref = CONCAT(
 SUBSTR(os_grid_ref,1,2), '5',
 SUBSTR(os_grid_ref,3,4), '5',
 SUBSTR(os_grid_ref,7,4)
 )
 WHERE ...;

If the table is huge, Update will take a long time. Leave off the WHERE clause if you want to change very row.

If you are changing the value as you fetch, then

SELECT ..., 
 CONCAT(...as above ...) AS new_ref
 FROM projects
 WHERE ...;

If you expect to do variations of this, consider writing a STORED FUNCTION to do the CONCAT.

• therobyouknow: +1 and accepted. Brilliant thanks so much. For my WHERE clause, I modified my original regex to now use: WHERE os_grid_ref REGEXP '^[A-Z]{2}[0-9]{4}[0-9]{4}$'; - which has the line anchors ^ and $ - this ensures that any entries already converted don't get converted again - so that the update query is idempotent after the first conversion, the regex acts as a conditional check in that regard.
• Rick James: @therobyouknow - That regexp is equivalent to '^[A-Z]{2}[0-9]{8}$'. This would also prevent double-conversion, but would not check for the full pattern: WHERE LENGTH(os_grid_ref) = 10, (and other possibilities)