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

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)

, ,
Previous Post
SQL SERVER – 2005 – Explanation of TRY…CATCH and ERROR Handling
Next Post
SQL SERVER – Running 64 bit SQL SERVER 2005 on 32 bit Operating System

Related Posts

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

    Reply
    • 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

      Reply
  • also, how can i modify this function to check if the first integer of the output is a “1”, if so, delete it.

    Reply
  • 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.

    Reply
  • 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)

    Reply
  • 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 ?

    Reply
  • I really am delighting in your blog I observed it via yahoo yesterday.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Uhm, actually, that posted function above *doesn’t* work… sorry!

    Reply
  • 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.

    Reply

Leave a Reply

Menu