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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

One Comment on “SQL function to extract number from string”

Leave a Reply

Your email address will not be published. Required fields are marked *