locked
Get only Numeric values from string with alphanumeric values in SQL RRS feed

  • Question

  • Hello Everyone

    I have string like 'DP_191_YTD' or 'DP_489_GPR' . Now I want only numbers from these strings. so results look like this - '191' or '489'.

    I created function and used it and it works fine. But i want to know if there is any other alternative to get same results.

    Please let me know your suggestions.

    Here is the function which works fine.

    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

    Monday, May 6, 2013 2:28 PM

Answers

  • Here is my quick re-write:

    use AllTests
    go
    
    ALTER FUNCTION dbo.udf_GetNumeric
    (@strAlphaNumeric VARCHAR(256))
    
    RETURNS
    TABLE
    AS
    RETURN
    WITH cte as (select @strAlphaNumeric as String, PATINDEX('%[0-9]%', @strAlphaNumeric) as NumberStart),
    cte1 as (SELECT String, 
    case when NumberStart>0 then substring(String, NumberStart, LEN(String)) ELSE '' END as Rest
    from cte),
    cte2 as (select String, Rest, patindex('%[^0-9]%', Rest) as NumberEnd from cte1)
    
    select String, case when NumberEnd > 0 then SUBSTRING(Rest, 1, NumberEnd-1) else Rest end as NumberOnly
    from cte2
    
    
    GO
    -- Tests
    select * from dbo.udf_GetNumeric('ABDC12345skjdksdjks');
    select  * from dbo.udf_GetNumeric('2345skjdksdjks');
    select * from dbo.udf_GetNumeric('ABDC12345');
    select * from dbo.udf_GetNumeric('ABDCskjdksdjks');


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, May 6, 2013 3:07 PM

All replies

  • Your solution should be fine. As alternative, you can convert your function to inline table valued function. The other alternative will be to use CLR function.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, May 6, 2013 2:30 PM
  • Thanks Naomi for your response.

    How can i convert above udf to inline table valued function?


    Monday, May 6, 2013 2:53 PM
  • Here is my quick re-write:

    use AllTests
    go
    
    ALTER FUNCTION dbo.udf_GetNumeric
    (@strAlphaNumeric VARCHAR(256))
    
    RETURNS
    TABLE
    AS
    RETURN
    WITH cte as (select @strAlphaNumeric as String, PATINDEX('%[0-9]%', @strAlphaNumeric) as NumberStart),
    cte1 as (SELECT String, 
    case when NumberStart>0 then substring(String, NumberStart, LEN(String)) ELSE '' END as Rest
    from cte),
    cte2 as (select String, Rest, patindex('%[^0-9]%', Rest) as NumberEnd from cte1)
    
    select String, case when NumberEnd > 0 then SUBSTRING(Rest, 1, NumberEnd-1) else Rest end as NumberOnly
    from cte2
    
    
    GO
    -- Tests
    select * from dbo.udf_GetNumeric('ABDC12345skjdksdjks');
    select  * from dbo.udf_GetNumeric('2345skjdksdjks');
    select * from dbo.udf_GetNumeric('ABDC12345');
    select * from dbo.udf_GetNumeric('ABDCskjdksdjks');


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, May 6, 2013 3:07 PM
  • Thanks Naomi for your help
    Monday, May 6, 2013 3:16 PM
  • Instead of thinking in procedural loops and functions, think declarative coding! Nest REPLACE() functions to convert alphas and underscores to empty strings . It is a common idiom, and you can google it. Some day T-SQL will have the ANSI Standard  TRANSLATE, but we have to fake until then.  Here is an example for removign digits

     REPLACE ('9' WITH '' IN
    REPLACE ('8' WITH '' IN
    REPLACE ('7' WITH '' IN
    REPLACE ('6' WITH '' IN
    REPLACE ('5' WITH '' IN
    REPLACE ('4' WITH '' IN
    REPLACE ('3' WITH '' IN
    REPLACE ('2' WITH '' IN
    REPLACE ('1' WITH '' IN
    REPLACE('0' WITH '' IN something)
    ))))))))), 

    Later, we can replace the REPLACE() calls with a call to the TRANSLATE (proj_id USING RemoveDigit) function without having to change any other code.

    But more important, do you have a CHECK() constraint on the DDL to prevent bad data? 
    CHECK (foobar LIKE 'DP_[0-9][0-9][0-9]-YTD'
          OR foobar LIKE 'DP_[0-9][0-9][0-9]-GPR')


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, May 6, 2013 4:13 PM
  • Thanks CELKO for your response. If i use your code, it will eliminate the numbers, right? But i need to eliminate the alphabates and keep only numbers.

    Thanks

    Monday, May 6, 2013 6:02 PM
  • As I said, this is a quick example I could cut & paste. You have to write your own code. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, May 6, 2013 7:22 PM
  • You first eliminate the numbers and then you use the resulting string (the one where you eliminated numbers) to retrieve the number from the original string. This is going to work assuming you only have 1 number in the string.

    But I suggest to stick with the approach I provided using 2 patindex.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, May 6, 2013 7:45 PM