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))
@intAlpha INT
@intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
@intAlpha > 0
@strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )

/* 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';

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

About these ads

24 thoughts on “SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only

  1. 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…


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

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


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

    Pinal Dave

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

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

    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

    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)

    IF (@alpha IS NOT NULL AND @alpha != ”) BEGIN
    SET @decimal = convert(decimal(14, 5), substring(@alpha, 1, (@CommaPos – 1))
    + ‘.’
    + substring(@alpha, (@CommaPos + 1), 20))
    — 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

    / Christofer

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

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

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


    How do I do this?

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

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

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

    DECLARE @intAlpha INT
    DECLARE @intNumber INT

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

    WHILE @intAlpha > 0
    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


    RETURN ISNULL(@strAlphaNumeric,0)


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