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

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

25 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…

    nitin

    Like

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

    Like

  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)

    Regards,
    Pinal Dave

    Like

  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

    Like

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

    Like

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

    Like

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

    Like

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

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

    Like

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

    Like

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

    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