SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only

SQL is great with String operations. Many times, I use T-SQL to do my string operation. Let us see User Defined Function, which I wrote few days ago, which will return only Numeric values from AlphaNumeric values.

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE
@intAlpha INT
SET
@intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE
@intAlpha > 0
BEGIN
SET
@strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN
ISNULL(@strAlphaNumeric,0)
END
GO

/* Run the UDF with different test values */
SELECT dbo.udf_GetNumeric('') AS 'EmptyString';
SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@';
SELECT dbo.udf_GetNumeric('123456') AS '123456';
SELECT dbo.udf_GetNumeric('asdf') AS 'asdf';
SELECT dbo.udf_GetNumeric(NULL) AS 'NULL';
GO

SQL SERVER - Get Numeric Value From Alpha Numeric String - UDF for Get Numeric Numbers Only parseint

As result of above script self explainary, I will be not describing it in detail. If you use any alternative method to do the same task, please let me know.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – Book Review – Pro SQL Server 2005 Replication (Definitive Guide)
Next Post
SQL SERVER – Introduction and Example of UNION and UNION ALL

Related Posts

33 Comments. Leave new

  • As always, your answer to a irritating problem has saved me a lot of time. Thank you.

    Reply
  • Jan Vejskrab
    July 3, 2014 7:30 pm

    Hi Dave,
    thanks a lot, it saved us some time.
    When a long string is passed to the function, the function will iterate char by char from the second occurance of non-numeric char.
    I made a small improvement, so function is not iterating across a series of non-numeric chars.

    CREATE FUNCTION dbo.FGetNumericFromAlphaNumericString (
    @strAlphaNumeric VARCHAR(max)
    )
    RETURNS VARCHAR(max)
    AS
    BEGIN

    DECLARE @intAlpha INT
    DECLARE @intNumber INT

    SET @intAlpha = PATINDEX(‘%[^0-9]%’, @strAlphaNumeric)
    SET @intNumber = PATINDEX(‘%[0-9]%’, @strAlphaNumeric)

    BEGIN
    WHILE @intAlpha > 0
    BEGIN
    SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, @intNumber – 1, ” )

    SET @intAlpha = PATINDEX(‘%[^0-9]%’, @strAlphaNumeric )
    SET @intNumber = PATINDEX(‘%[0-9]%’, SUBSTRING(@strAlphaNumeric, @intAlpha, LEN(@strAlphaNumeric)) )
    SELECT @intNumber = CASE WHEN @intNumber = 0 THEN LEN(@strAlphaNumeric) ELSE @intNumber END

    END
    END

    RETURN ISNULL(@strAlphaNumeric,0)

    END
    GO

    Reply
  • Thanks man, If I want the logic to take only the numeric number that appears before the “_”, . that is if I have “AMT_ef_bc_21123_45.AF then I want to get 21123 and not 2112345. Thanks for your help.

    Reply
  • Suman Kalyan Sahoo
    April 3, 2015 1:04 am

    SELECT NAME
    ,STUFF(substring(NAME, PatIndex(‘%[0-9]%’, NAME), len(NAME)), PatIndex(‘%[^0-9]%’, substring(NAME, PatIndex(‘%[0-9]%’, NAME), len(NAME))), (len(substring(NAME, PatIndex(‘%[0-9]%’, NAME), len(NAME))) – (PatIndex(‘%[^0-9]%’, substring(NAME, PatIndex(‘%[0-9]%’, NAME), len(NAME)))) + 1), ”)
    FROM #test

    You can achieve this using a select query too.without using a while loop and function.

    Reply
  • I have a product names like this SAN Ports – 6 EMC/HDS Tier 1 864GB,SAN Ports – 8 EMC/HDS Tier 1 1152GB. I need to extract 864 and 1152 for the GB value only.How do I get the values.I have used getnumeric functions(using PATINDEX) but it returns 1684 and 811152 like this.Please help me to solve the issue.its very urgent

    Reply
  • P.V.Rajagopal
    June 3, 2015 10:02 am

    Really Good Job.Thank You.

    Reply
  • Spencer Bertrand
    February 24, 2016 11:47 pm

    If anyone is having a problem that is stating that “the function already exists…” etc. Try changing CREATE to ALTER. Just FYI

    Reply
  • Mahesh Palem
    June 29, 2016 1:15 pm

    Hi There is just a six digit number in my column data. How to do it then?

    Reply
  • Hi Pinal – I have always gotten help from your post but this the first time I am posting a question. Please help.

    1) I have a column which has pharmacy name, street Address, city, State, Zip code. I need to put address in separate fields.
    Field starts with Pharmacy name and then hyphen’-‘ and the address in different order like sometimes it has city and state after hyphen and then street address and other times its street address following hyphen. I am able to get just the pharmacy name just looking for the first hyphen but it’s challenging to get the street address which is starting with a number.

    Alia.

    Reply
  • Hi Pinal,

    is it possible from this query to select only big int from the column name

    eg

    apple12_5678_9apple –> 5678

    apple12-567_78901 –> 78901

    Thanks
    Shankar

    Reply
  • how can I get these number to be separated by commas like
    before using function string : ‘asdhh126728,yuiyiu23333 40030j kjjj 88’

    result should be : ‘126728,23333,40030,88’

    Reply

Leave a Reply

Menu