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
Reference : Pinal Dave (https://blog.sqlauthority.com)
39 Comments. Leave new
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.