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

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.

SQL SERVER - UDF - User Defined Function to Extract Only Numbers From String alpha

 

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

SQL SERVER - UDF - User Defined Function to Extract Only Numbers From String result

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)

Solarwinds
, ,
Previous Post
SQL SERVER – Simple Method to Generate Fixed Digit OTP Code Using Random Function
Next Post
SQL SERVER – Rename Logical Database File Name for Any Database

Related Posts

4 Comments. Leave new

Leave a Reply

Menu