CODEWITHSUNDEEP
mysqlsql
Kavi Kumaran
Kavi Kumaran

Reputation: 21

SQL -Extracting specific number pattern in a column

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.

Upvotes: 0

Views: 87

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

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)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

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

Demo

Upvotes: 1

Dinu
Dinu

Reputation: 1524

UPDATE table SET col2=REGEXP_REPLACE(col1,'^(.*[^\\d])?(\\d{4})?([^\\d].*)?$','\\2');

Upvotes: 0

Related Questions