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 (http://blog.SQLAuthority.com)

About these ads

39 thoughts on “SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String

  1. can you please help me how to extract date and time from string? here is my example:

    person A at 2006 345 15:24 See attachment from Ron Holden person B at 2006 361 16:20 Refer to AR 113831.

    i would like output will be like : 2006 345 15:24

    I ready appreciate for all your helped and supported. I looking forward to hear from you soon.

    Big Thanks,

    Tammy

    Like

    • declare @s varchar(1000)
      set @s=’person A at 2006 345 15:24 See attachment from Ron Holden person B at 2006 361 16:20 Refer to AR 113831.’
      select substring(@s,patindex(‘%[0-9]%’,@s),patindex(‘%:%’,@s)+2-patindex(‘%[0-9]%’,@s)+1)

      Like

  2. Hi

    First of all, thank you for taking time to write t-sql and publish it.

    Second, I would like to point out that substring is 1 based instead of 0 based (in SQL 2000 Sp3 at least but should remain the same for other versions as well)

    What does it mean? Little beside that 0 based loop one time more than it really need. (One loop is wasted)

    The fix? Set @count = 1 for the starting condition instead of @count=0

    Like

  3. Hi Pinal,
    I always follow your statement which are really helpful in my work.
    I am just wondering if you have function or modify the above function in reverse way. Meaning I would just like to extract the letters from Alphanumeric string.
    Like I have a fields like.
    AK48148
    D144432
    MT23432
    DT55544

    So I want a function that just extract the letters from it.
    I would appreciate your time.

    Thanks

    Like

  4. @Syed,

    I am sure there must be be another good way of doing the same thing…. I came up with this… This is HIGHLY performance killing query… since this is a while loop under while loop.

    Its your choice if you want to use this…

    This does exactly what you want.

    Here you go…

    — This is your main table
    create table example123 ( names varchar(max))
    go
    — Inserting Sample Data
    insert into example123 values ( ‘AK48148′)
    insert into example123 values ( ‘D144432′)
    insert into example123 values ( ‘MT23432′)
    insert into example123 values ( ‘DT55544′)
    insert into example123 values ( ‘12345’) — This will not be displayed in the output since it do no have any letter in it

    — This is the output or result table
    create table #results ( names varchar(100))
    go
    — This is our sample original table
    create table #result ( id int identity, names varchar(100))
    go
    — We are copying your data into this sample original table
    insert into #result select names from example123
    go
    — This is Actual Script
    declare @var int
    declare @var2 int
    declare @car varchar(100)
    declare @len int
    declare @res varchar(100)
    declare @result varchar(100)
    declare @store varchar(100)
    set @var = 1
    while @var < = ( select count(*) from #result)
    begin
    select @car = names from #result where id = @var
    set @result = ”
    set @len = 1
    while @len < = ( select len(@car) from #result where id = @var)
    begin
    select @store = substring ( @car, @len, len(@car)) from #result where id = @var
    if ascii( @store) 64
    begin
    select @res = char(Ascii (@store)) from example123 where ascii( @store) 64
    set @result = @res+@result
    end
    set @len= @len+1
    end
    if @result ”
    begin
    insert into #results select reverse(@result)
    end
    set @var= @var+1
    end
    select * from #results
    drop table #results
    drop table #result
    drop table example123
    — End of Script

    I am not sure if there is any function already to do this, if you want to change the code, you are more than welcome, if something better can written for same, please post

    Hope this helps,
    Imran.

    Like

    • Heres an alternative method that seems to have better performance. The method described above took about 30 seconds over my test data whereas the method below takes about 11 seconds over the same data.

      create function [dbo].[ExtractNumbers](@Numbers nvarchar(2000))
      returns nvarchar(2000)
      as
      BEGIN

      declare @NonNumericIndex int
      set @NonNumericIndex = PATINDEX(‘%[^0-9]%’,@Numbers)

      WHILE @NonNumericIndex > 0
      begin
      SET @Numbers = REPLACE(@Numbers,SUBSTRING(@Numbers,@NonNumericIndex,1),”)
      set @NonNumericIndex = PATINDEX(‘%[^0-9]%’,@Numbers)
      end

      return @Numbers

      END

      Like

  5. @Syed,

    Please replace the middle part of the query with this, I dont know how the query is changed, this is not the one I posted… I executed this query atleast 10 times befire posting…. There must be something wrong happened while posting my reply,

    anyways I apologize for any inconvenience caused,

    Please replace this query in the end of the code

    – This is Actual Script
    declare @var int
    declare @var2 int
    declare @car varchar(100)
    declare @len int
    declare @res varchar(100)
    declare @result varchar(100)
    declare @store varchar(100)
    set @var = 1
    while @var < = ( select count(*) from #result)
    begin
    select @car = names from #result where id = @var
    set @result = ”
    set @len = 1
    while @len 64 and ascii(@store) 64 and ascii (@store) < 123 — This is the correct script

    set @result = @res+@result
    end
    set @len= @len+1
    end
    if @result ”
    begin
    insert into #results select reverse(@result)
    end
    set @var= @var+1
    end
    select * from #results
    drop table #results
    drop table #result
    drop table example123
    – End of Script

    Hope this helps.
    Imran.

    Like

  6. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authority with Pinal Dave

  7. plz help me i have a table which contained alpha numeric values like this 1235abcd7412 nofixed length and other row contained abcvd52a1a2

    account
    1235abcd7412
    abcfd52a1a2
    ghti5214ve3
    asif214bv2

    nofixed length account column plz show me easy query

    thanks
    asif

    Like

  8. Reply to Asif,

    @Asif

    CREATE FUNCTION [dbo].[UFN_Eliminate_Alphabet] (@string varchar(max))
    RETURNS int
    AS
    BEGIN
    DECLARE @result varchar(50)
    DECLARE @position int
    SET @position = 1
    SET @result = ”

    WHILE @position <= DATALENGTH(@string)
    BEGIN
    SELECT @result = @result + case when (ASCII(SUBSTRING(@string, @position, 1))) between 48 and 57 then SUBSTRING(@string, @position, 1)
    else ''
    end
    SET @position = @position + 1
    END
    RETURN ( select convert ( int, @result))
    END
    GO
    — Sample to Execute
    select [dbo].[UFN_Eliminate_Alphabet]('abc64h5n5u')
    GO
    CREATE FUNCTION [dbo].[UFN_Eliminate_Numbers] (@string varchar(max))
    RETURNS varchar (max)
    AS
    BEGIN
    DECLARE @result varchar(50)
    DECLARE @position int
    SET @position = 1
    Set @result = ''
    WHILE @position <= DATALENGTH(@string)
    BEGIN
    SELECT @result = @result + case when ASCII(SUBSTRING(@string, @position, 1)) between 97 and 122 then SUBSTRING(@string, @position, 1)
    when ASCII(SUBSTRING(@string, @position, 1)) between 65 and 90 then SUBSTRING(@string, @position, 1)
    else ''
    end
    SET @position = @position + 1
    END
    RETURN ( select @result As ID )
    END
    GO
    — Sample to Execute
    select [dbo].[UFN_Eliminate_Numbers]('abc64h5n5u')
    GO

    Like

  9. Reply to Asif,

    First function

    CREATE FUNCTION [dbo].[UFN_Eliminate_Alphabet] (@string varchar(max))
    RETURNS int
    AS
    BEGIN
    DECLARE @result varchar(50)
    DECLARE @position int
    SET @position = 1
    SET @result = ”

    WHILE @position <= DATALENGTH(@string)
    BEGIN
    SELECT @result = @result + case when (ASCII(SUBSTRING(@string, @position, 1))) between 48 and 57 then SUBSTRING(@string, @position, 1)
    else ''
    end
    SET @position = @position + 1
    END
    RETURN ( select convert ( int, @result))
    END
    GO
    — Sample to Execute
    select [dbo].[UFN_Eliminate_Alphabet]('abc64h5n5u')

    ~IM

    Like

  10. This comment is sent to me in email by our SQL Expert Imran Mohammed.

    Reply to Asif,

    @Asif

    CREATE FUNCTION [dbo].[UFN_Eliminate_Alphabet] (@string varchar(max))
    RETURNS int
    AS
    BEGIN
    DECLARE @result varchar(50)
    DECLARE @position int
    SET @position = 1
    SET @result = ”

    WHILE @position <= DATALENGTH(@string)
    BEGIN
    SELECT @result = @result + case when (ASCII(SUBSTRING(@string, @position, 1))) between 48 and 57 then SUBSTRING(@string, @position, 1)
    else ''
    end
    SET @position = @position + 1
    END
    RETURN ( select convert ( int, @result))
    END
    GO
    — Sample to Execute
    select [dbo].[UFN_Eliminate_Alphabet]('abc64h5n5u')
    GO
    CREATE FUNCTION [dbo].[UFN_Eliminate_Numbers] (@string varchar(max))
    RETURNS varchar (max)
    AS
    BEGIN
    DECLARE @result varchar(50)
    DECLARE @position int
    SET @position = 1
    Set @result = ''
    WHILE @position <= DATALENGTH(@string)
    BEGIN
    SELECT @result = @result + case when ASCII(SUBSTRING(@string, @position, 1)) between 97 and 122 then SUBSTRING(@string, @position, 1)
    when ASCII(SUBSTRING(@string, @position, 1)) between 65 and 90 then SUBSTRING(@string, @position, 1)
    else ''
    end
    SET @position = @position + 1
    END
    RETURN ( select @result As ID )
    END
    GO
    — Sample to Execute
    select [dbo].[UFN_Eliminate_Numbers]('abc64h5n5u')
    GO

    Like

  11. how can i get all numeric values as a table ?

    i mean, the result should be in a table like that..

    Number
    3
    323
    111

    not scalar varchar value…

    Thanks

    Like

  12. Hi all i need a small help,
    I am having my string like this AP1BC_77.. I would like to display only 77 like that if we have any sort of string but i would like to display only the numeric appended to the end of the characters. Can any one help me please..

    Like

  13. 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

    Like

    • 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

      Like

  14. 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.

    Like

  15. 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)

    Like

  16. 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 ?

    Like

  17. 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.

    Like

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

  19. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s