SQL function to extract number from string

A common question among forum is a SQL function to extract number from string. Let’s see how this can be achieved.

One way to achieve this is with while loop as shown below.

DECLARE @string varchar(100)

SET @string = '533##dfd123fdfd111,'

-- loop till there are characters left in a string
WHILE PATINDEX('%[^0-9]%',@string) <> 0
BEGIN
    -- replace characters with empty space
    SET @string = STUFF(@string,PATINDEX('%[^0-9]%',@string),1,'')
END
SELECT @string

The logic is to loop through the string and replace all characters with empty string until we are only left with numbers. The PATINDEX(‘%[^0-9]%’,@string) returns the starting position of a character/alphabet. So we loop until it returns 0 i.e. there are no more alphabets/characters present in a string. It then replaces the characters/alphabets with empty space with in the while loop. Thus, we are left only with numbers once the while loop finishes.

The T-SQL is a set based language. The solution given below uses tally table to extract numbers in a set based way.

DECLARE @string varchar(100)

SET @string = '533##dfd123fdfd111,'

;WITH T1(number) AS (SELECT 1 UNION ALL SELECT 1),
T2(number)  AS (SELECT 1 FROM T1 AS a cross join T1 as b),
T3(number)  AS (SELECT 1 FROM T2 AS a cross join T2 as b),
T4(number)  AS (SELECT 1 FROM T3 AS a cross join T3 as b),
Nums(number) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
from T4)

SELECT STUFF( 
(SELECT   '' + SUBSTRING(@String,Nums.number,1)
FROM Nums
WHERE ISNUMERIC(SUBSTRING(@String,Nums.number,1))=1
FOR XML PATH('')),1,0,'')

The above query breaks the string into rows of single characters and then concatenates only the rows with numeric values, thus eliminating all characters/alphabets.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook