The use case looks pretty much like this :
Column1
Test1234
12Test5678
Test6789sample
The objective is to extract the 4 Digit numbers (sure about the length) present within the string and store it in a separate column. The situation becomes much trickier with case 2 where the string has numbers in which we are not interested.
If you are using MySQL 8+, then we can take a two-step approach using REGEXP_REPLACE and REGEXP_SUBSTR. First, we can strip all groups of digits occurring five or more times. Then we can find a remaining digit group of 4 using REGEXP_SUBSTR.
WITH yourTable AS (
SELECT 'Test6789sample1234567' AS col
)
SELECT
REGEXP_SUBSTR(REGEXP_REPLACE(col, '[0-9]{5,}', ''), '[0-9]{4}')
FROM yourTable;
This outputs: 6789
UPDATE table SET col2=REGEXP_REPLACE(col1,'^(.*[^\\d])?(\\d{4})?([^\\d].*)?$','\\2');
1234abcd, because your pattern asserts that something always comes before the 4 digits to be matched.In MySQL 8+, you would simply use REGEXP_SUBSTR():
REGEXP_SUBSTR(column1, '[0-9]{4}')
This is much trickier in earlier versions. You might need a case:
select (case when column1 regexp '^[0-9]{4}' then substr(column1, 1, 4)
when column1 regexp '^[^0-9]{1}[0-9]{4}' then substr(column1, 2, 4)
when column1 regexp '^[^0-9]{2}[0-9]{4}' then substr(column1, 3, 4)
. . . -- repeat as many times as needed
end)
