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)

, ,
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

34 Comments. Leave new

  • thanks, for me will be useful

    Reply
  • hi pinal…..
    with the help of this we can not remove whole alphabets in our string it removes only first group occurence of alphabets e.g. ‘qwert12354asd34as’ the result of this string will be 12354asd34as as per requirment output should be 1235434………..it need one more while loop…

    nitin

    Reply
  • Nitin: I posted an alternate solution here that does what you need, but Mr. Dave has not bothered to approve the comment.

    Reply
  • Trying again:

    http://sql blog.com/blogs/adammachanic/archive/2006/07/12/patternbasedreplacementudf.aspx

    Reply
  • Hi Nitin,
    The above logic is giving the required result.

    I think you might have missed somewhere. Please recheck it at your end.

    Thanks,
    Nizam

    Reply
  • How does this accomodate decimals? Like this:

    ‘Price: 3.56783’

    I would just want to return 3.56783

    Reply
  • Hello Andrew,

    In the function dbo.udf_GetNumeric replace the line:

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

    with following line at two places:

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

    Regards,
    Pinal Dave

    Reply
  • Another method

    Madhivanan

    Reply
  • Hello sir

    My senario like this , I have one text box and one table called student(Name,Roll_No) and one Insert button to insert data in DB. If i type “amit-28” in textbox then by clicking on Insert button “amit” should inserted into Name field and “28” should inserted into RollNo field. So what code to write??or query ??

    Thanking You

    Reply
    • You can easily use split function in the application

      dim sp
      sp=split(string,”-“)

      Now you can access first and second values by

      sp(0) and sp(1)

      Reply
  • This really worked well,thanks

    Reply
  • To handle both decimal and non-decimal numbers I’ve created this procedure to handle e.g. ‘ABC355,88ghf’ to 355,88 and ‘ABC 78 QQ’ to 78,00 etc:

    CREATE PROCEDURE [dbo].[CleanDataFromAlpha]
    @alpha varchar(50),
    @decimal decimal(14, 5) OUTPUT
    AS BEGIN
    SET NOCOUNT ON;

    DECLARE @ErrorMsg varchar(50)
    DECLARE @Pos int
    DECLARE @CommaPos int
    DECLARE @ZeroExists int
    DECLARE @alphaReverse varchar(50)
    DECLARE @NumPos int
    DECLARE @Len int

    — 1 Reverse the alpha in order to get the last position of a numeric value
    SET @alphaReverse = REVERSE(@alpha)
    — 2 Get the last position of a numeric figure
    SET @NumPos = PATINDEX(‘%[0-9]%’, @alphaReverse)
    — 3 Get the lenght of the string
    SET @Len = LEN(@alpha)
    — 4 Add a comma after the numeric data in case it’s no decimal number
    SET @alpha = SUBSTRING(@alpha, 1, (@Len – @NumPos + 1))
    + ‘,’
    + SUBSTRING(@alpha, (@Len – @NumPos + 2), 50)

    — Check if there is a zero (0) in the @alpha, then we later set the @decimal to 0
    — if it’s 0 after the handling, else we set @decimal to NULL
    — If 0 no match, else there is a match
    SET @ZeroExists = CHARINDEX ( ‘0’ , @alpha ,1 )

    — Find position of , (comma)
    SET @CommaPos = 1
    SET @CommaPos = PATINDEX(‘%,%’, @alpha)
    IF (@CommaPos = ”) BEGIN
    SET @CommaPos = 20
    END

    SET @Pos = PATINDEX(‘%[^0-9]%’,@alpha)
    — Replaces any aplha with ‘0’ since we otherwice can’t keep track of where the decimal
    — should be put in. We assume the numeric number has no aplhe inside. The regular way
    — to solve this is to replace with ”, but then we miss the way to find the place to
    — put in the decimal.
    WHILE (@Pos > 0) BEGIN
    SET @alpha = STUFF(@alpha, @pos, 1, ‘0’)
    SET @Pos = PATINDEX(‘%[^0-9]%’,@alpha)
    END

    IF (@alpha IS NOT NULL AND @alpha != ”) BEGIN
    SET @decimal = convert(decimal(14, 5), substring(@alpha, 1, (@CommaPos – 1))
    + ‘.’
    + substring(@alpha, (@CommaPos + 1), 20))
    END
    — Since we in this case don’t want to set 0 if where is no numeric value, we set NULL to be safe
    IF (@decimal = 0 AND @ZeroExists = 0) BEGIN
    SET @decimal = NULL
    END
    END

    / Christofer

    Reply
  • Thanks a lot for your function..

    Reply
  • You are fantastic!!!! saved me tons of work!

    Reply
  • Amit Srivastava
    June 12, 2012 10:12 pm

    Thank alot

    Reply
  • Thanks, But if I use it on 1 lakh rows with 5 numerical column data it takes more than 10 min. so please help me to resolve it.

    Reply
  • Hi,

    My issue is somehow related to this one. I want to extract last 10 digits from a float column

    I have values such as 12343456678
    and I want to extract the last 10 digits only (2343456678). I haven’t had success googling this for a while now.

    Please remember the column I have has all numeric data and is chosen to be float.

    Reply
  • srikant rahul
    May 30, 2013 5:19 pm

    Really save a lot of work

    Reply
  • Product 000770420 DELL E420-A0 42″ 1080P LED LCD HDTV P=12
    Product 000770470 DELL E470I-A0 47″ 1080P LED LCD HDTV P=7
    Product 000770701 DELL E701I-A3 70″ CLASS 1080P LED SMART TV P=4
    Product 000791339 DELL E390I-A1 39″ 1080P SMART LED LCD P=28

    The above is the sample data. I just need to extract the first set of numbers like

    000770420
    000770470
    000770701
    000791339

    How do I do this?

    Reply
  • Thank you Dave. for me It works without any issue :)

    Reply
  • Helloo,
    I Have function that return string value like ‘(4+2)*(3-1)’
    Then how to compute this value?? as math .

    I need result as : 12

    Please help me….
    thanks in advance…. pls pls

    Reply

Leave a Reply

Menu