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.