This user defined function will extract only numbers from string values.
Following SQL User Defined Function will extract/parse numbers from the string.
CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9' BEGIN SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1) END SET @Count = @Count + 1 END
RETURN @IntNumbers
END
GO
Run following script in query analyzer.
SELECT dbo.ExtractInteger('My 3rd Phone Number is 323-111-CALL')
GO
It will return following values.
3323111
What to Know Before You Extract Only Numbers From String Values
The function walks through the input one character at a time and keeps each character between 0 and 9. That is why the sample returns 3323111: the 3 from 3rd is kept too, and every digit is joined into one string. It does not pick out separate numbers, it collects all the digits.
A few behaviors to know before you use it on real data:
- The result is
VARCHAR, so leading zeros stay. That is good for phone numbers and postal codes. - If the input has no digits, or is NULL, you get an empty string back, not NULL.
- Decimal points and minus signs are dropped, so 12.50 becomes 1250 and -7 becomes 7.
- The parameter is
VARCHAR(2000), so longer text is cut off before the function sees it.
If you need a number instead of text, convert the result after checking that it is not empty, for example with CAST to BIGINT. A long run of digits can still be too big for INT.
Performance is the other thing to watch. A scalar function like this runs once per row, and its loop runs once per character. On a few thousand rows that is fine. On millions of rows it can be slow, so I use this kind of function for cleanup jobs and one time fixes rather than inside busy queries.
When you test it, try a few tricky values: a string with only letters, an empty string, a NULL, and strings that start or end with digits. Those cases show quickly whether the output is what you expect.
If you only need to know whether a value contains any digit at all, you do not need the function. PATINDEX('%[0-9]%', @String) > 0 answers that question in one step, and it is much cheaper than walking through every character.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
39 Comments. Leave new
I’m using your “integer extractor” function to output a temporary field which I’ll use to sort a list. only problem is, the output is varchar and therefore the numbers are sorting correctly. how can i convert the output to an INT datatype.
my query:
SELECT dbo.ExtractNumbers(pub) as newcol, title from test_table
order by newcol desc
disregard me first question, i figured it out:
SELECT cast(dbo.ExtractNumbers(pub)as int) as newcol, pub from test_table order by newcol desc
also, how can i modify this function to check if the first integer of the output is a “1”, if so, delete it.
I would like a series of data that looks like this
D1
D10
D2
D1AW
To sort like this
D1
D1AW
D2
D10
I would very much appreciate any help with this sort.
There is a point in yout SQL coding standard that
• Always put the DECLARE statement at the starting of the code in the stored procedure. This will make the query optimizer to reuse query plans.
What does it mean “the query optimizer to reuse query plans.”?
And one more doubt which is the best practice of following the declare statement.
Is it Declare @variable1 int,@variable2 varchar(10)
or Declare @variable1 int
Declare @variable2 varchar(10)
Hi ,
I want to create one function which shold return result of
select query which is just column with top 10 values.
Bit of code will help.
so basically fuction will return those top 10 vales of one restult columnt. What type of variable shold i take for returnig those 10 values ?
Select top * from table
order by col
I really am delighting in your blog I observed it via yahoo yesterday.
Hi Pinal ,
Should the LEN(@String) in the query above be passed to avariable and then compare the while loop to a variable. Is there a performance difference between the two menthods.
Here’s a much simpler version:
CREATE FUNCTION [dbo].[fnNumbersFromStr](@str varchar(8000))
returns varchar(8000)
AS
/*
SELECT [dbo].[fnNumbersFromStr](‘333steve222 444%$@!@!_+!#)(*&!@#}|{“:?>,.,//”;`~’)
SELECT [dbo].[fnNumbersFromStr](‘0’)
*/
BEGIN
IF(@str IS NULL) OR (@str = ”)
RETURN ”
WHILE patindex(‘%[^0-9]%’,@str)>0
SET @str = rtrim(ltrim(replace(@str,substring(@str,patindex(‘%[^0-9]%’,@str),1),”)))
RETURN @str
END
Uhm, actually, that posted function above *doesn’t* work… sorry!
Once again you’ve saved me time with your generosity. Could I have written this? Sure. But yours is perfect, and when I needed it. Thank you.
Thanks Jerry.