How to extract numbers from a string using TSQL

Question

I have a string:

@string='TEST RESULTS\TEST 1\RESULT 1

The string/text remains the same except for the numbers

1. need the 1 from TEST
2. need 1 from RESULT

to be used in a query like:

SET @sql =  "SELECT *
            FROM   TABLE
            WHERE  test = (expression FOR CASE 1 resulting IN INT 1)
                   AND result = (expression FOR CASE 2 resulting IN INT 1)"

Answer 1

Looks like you already have a solution that met your needs but I have a little trick that I use to extract numbers from strings that I thought might benefit someone. It takes advantage of the FOR XML statement and avoids explicit loops. It makes a good inline table function or simple scalar. Do with it what you will :)

DECLARE @String varchar(255) = 'This1 Is2 my3 Test4 For Number5 Extr@ct10n';


SELECT
    CAST(( 
        SELECT CASE --// skips alpha. make sure comparison is done on upper case
            WHEN ( ASCII(UPPER(SUBSTRING(@String, Number, 1))) BETWEEN 48 AND 57 )
            THEN SUBSTRING(@String, Number, 1)
            ELSE ''END
        FROM
        ( 
            SELECT TOP 255 --// east way to get a list of numbers
                                           --// change value as needed.
                ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS Number
             FROM master.sys.all_columns a
                CROSS JOIN master.sys.all_columns b 
        ) AS n
        WHERE Number <= LEN(@String)
        --// use xml path to pivot the results to a row
        FOR XML PATH('') ) AS varchar(255)) AS Result

Result ==> 1234510

Answer 2

You can script an sql function which can used through your search queries. Here is the sample code.

CREATE FUNCTION udf_extractInteger(@string VARCHAR(2000))
RETURNS VARCHAR(2000)
    AS

    BEGIN
        DECLARE @count int
        DECLARE @intNumbers VARCHAR(1000)
        SET @count = 0
        SET @intNumbers = ''

        WHILE @count <= LEN(@string)
        BEGIN 
            IF SUBSTRING(@string, @count, 1)>='0' and SUBSTRING (@string, @count, 1) <='9'
                BEGIN
                    SET @intNumbers = @intNumbers + SUBSTRING (@string, @count, 1)
                END
            SET @count = @count + 1
        END
        RETURN @intNumbers
    END
    GO

QUERY :

SELECT dbo.udf_extractInteger('hello 123 world456') As output

OUTPUT: 123456

Referred from : http://www.ittutorials.in/source/sql/sql-function-to-extract-only-numbers-from-string.aspx