In my old post SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String, I showed you User Defined function which can be used to extract only numbers from the given string. In this post, I am going to show you another method of extracting numbers from the string.
The previous function was using a WHILE loop to identify the number and extract it. The new function will use the number table, also known as Tally table to split the characters and identify the number. I have now improved over my previous user defined function and created this function.
Let us create this new function
CREATE FUNCTION dbo.ExtractInteger_new(@String VARCHAR(2000)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @result VARCHAR(2000); SET @result=''; SELECT @result=@result +STRING FROM ( SELECT SUBSTRING(@string,NUMBER,1) AS STRING FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER>0 AND NUMBER<=LEN(@string) ) AS T WHERE STRING LIKE '%[0-9]%'; RETURN @result END GO
Now using the same old example call this function
SELECT dbo.ExtractInteger_new('My 3rd Phone Number is 323-111-CALL') as numbers
The result is 3323111
The logic is to split each character into separate row and then concatenate only the numbers by using the regular expression ‘%[0-9]%’
Note that MASTER..SPT_VALUES is a system table which can be used as Number table. If you do not want to use a system table, you can also create your own number table and use it.
If you have any suggestion, please leave a comment, I will be happy to share your version of UDF with everyone with due credit.
Reference: Pinal Dave (https://blog.SQLAuthority.com)