How To Extract Numbers From A String Using Tsql

I have a string: @string='TEST RESULTS\TEST 1\RESULT 1 The string/text remains the same except for the numbers need the 1 from TEST need 1 from RESULT to be used in a query like:

Solution 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@Stringvarchar(255) ='This1 Is2 my3 Test4 For Number5 Extr@ct10n';


SELECTCAST(( 
        SELECTCASE--// skips alpha. make sure comparison is done on upper caseWHEN ( ASCII(UPPER(SUBSTRING(@String, Number, 1))) BETWEEN48AND57 )
            THENSUBSTRING(@String, Number, 1)
            ELSE''ENDFROM
        ( 
            SELECT TOP 255--// east way to get a list of numbers--// change value as needed.ROW_NUMBER() OVER ( ORDERBY ( SELECT1 ) ) AS Number
             FROM master.sys.all_columns a
                CROSSJOIN master.sys.all_columns b 
        ) AS n
        WHERE Number <= LEN(@String)
        --// use xml path to pivot the results to a rowFOR XML PATH('') ) ASvarchar(255)) ASResult

Result ==> 1234510

Solution 2:

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

CREATEFUNCTION udf_extractInteger(@stringVARCHAR(2000))
RETURNSVARCHAR(2000)
    ASBEGINDECLARE@countintDECLARE@intNumbersVARCHAR(1000)
        SET@count=0SET@intNumbers=''

        WHILE @count<= LEN(@string)
        BEGIN 
            IF SUBSTRING(@string, @count, 1)>='0'andSUBSTRING (@string, @count, 1) <='9'BEGINSET@intNumbers=@intNumbers+SUBSTRING (@string, @count, 1)
                ENDSET@count=@count+1ENDRETURN@intNumbersEND
    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

Solution 3:

Since you have stable text and only 2 elements, you can make good use of replace and parsename:

declare@stringvarchar(100) ='TEST RESULTS\TEST 1\RESULT 2'selectcast(parsename(replace(replace(@string, 'TEST RESULTS\TEST ', ''), '\RESULT ', '.'), 2) asint) as Test
    , cast(parsename(replace(replace(@string, 'TEST RESULTS\TEST ', ''), '\RESULT ', '.'), 1) asint) asResult/*
       Test      Result
----------- -----------
          1           2
*/

The replace portion does assume the same text and spacing always, and sets up for parsename with the period.

Solution 4:

This method uses SUBSTRING, PARSENAME, and PATINDEX:

SELECTSUBSTRING(PARSENAME(c,2), PATINDEX('%[0-9]%',PARSENAME(c,2)), LEN(c)) Test,
  SUBSTRING(PARSENAME(c,1), PATINDEX('%[0-9]%',PARSENAME(c,1)), LEN(c)) ResultFROM (   SELECT REPLACE(@val, '\', '.') c) t

Use PARSENAME to split the string. The text of the string won't matter -- it will just need to contain the 2 back slashes to parse to 3 elements. Use PATINDEX with a regular expression to replace non-numeric values from the result. This would need adjusting if the text in front of the number ever contained numbers.

If needed, CAST/CONVERT the results to int or the appropriate data type.

Here is some sample Fiddle.

Good luck.