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

  • 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

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

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

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

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

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

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

    Reply
  • Moreno Vendramin
    November 26, 2008 4:12 pm

    thank you for this article.
    It was very helpful to me.

    Moreno

    Reply
  • SUBSTRING(@String,@Count,1) <= ‘9’

    what is the purpose of 9 here?

    Reply
  • @Reena,

    Because the minimum digit is 0 and maximum digit is 9.

    Regards,
    IM.

    Reply
  • how can i get only amount from this
    50.00 on 04/06/2009

    Reply
  • Imran Mohammed
    April 9, 2009 8:12 am

    @Hema

    select substring ( ‘50.00 on 04/06/2009’, 1, charindex (‘.’, ‘50.00 on 04/06/2009’)+2)

    ~IM

    Reply
    • Other methods

      declare @s varchar(100)
      set @s='50.00 on 04/06/2009'
      select substring ( @s, 1, charindex (' ', @s)-1)
      select substring (@s , 1, patindex ('%[0] %', @s))

      Also refer this post

      Reply
  • I want to isolate alphanumeric values like
    column a values 456abc123def
    I want to sepateit

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

    Reply
  • Brian Tkatch
    June 8, 2009 10:30 pm

    @asif

    Please provide sample output.

    Reply
  • Imran Mohammed
    June 9, 2009 9:03 am

    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

    Reply
  • Imran Mohammed
    June 9, 2009 9:04 am

    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

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

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

    Reply
  • Thanks a lot. Very helpful.

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

    Reply
  • declare @s varchar(100)
    set @s=’AP1BC_77′
    select substring(@s,charindex(‘_’,@s)+1,len(@s))

    Reply

Leave a Reply