How to extract numbers in middle of string-CodePudding

I am trying to learn SQL.

Currently I got stuck on how can I extract the numbers from A1B2C3 in a column. I want 123 as a result, can anyone help me?

I try to use regexp_extract on A1B2C3 yet the outcome only showing A.

CodePudding user response：

I would use a regex replacement here:

SELECT val, REGEXP_REPLACE(val, r'\D ', '') AS val_out
FROM yourTable;

The above approach works by stripping off all non digit characters, leaving behind only the numbers from the input string.

CodePudding user response：

SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt   't') - 1) 
FROM (
    SELECT subsrt = SUBSTRING(Item_Name, pos, LEN(Item_Name))
    FROM (
        SELECT Item_Name, pos = PATINDEX('%[0-9]%', Item_Name)
        FROM tblItem_Master
    ) d
) t

PATINDEX is an inbuilt function of SQL Server which returns the integer value from the string using parameters. There are 2 parameters in this function which is search pattern and expression.

In this example, Item_Name is field name and tblItem_Master is table name.