SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String – Number Table Method

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.