Find the Numbers from a string

  • Hi experts,

    i hope my problem will be solved. I have a string and i want to get only the numbers from right.

    for example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like

    '4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.

    I got a function where it gives me all the numbers in a string but I don't need that

    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

    SELECT dbo.udf_GetNumeric('111zxfggr123456') AS 'Num'

    if i ran the select statement it gives me the result 111123456 but i want only 123456 or if i select

    SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.

    Can any one help me out to solve this?

    Thanks in advance!!

  • DECLARE @S AS VARCHAR(100)='123756zxfggr123456';

    SELECT REVERSE(

    SUBSTRING(REVERSE(@s),PATINDEX('%[0-9]%',

    REVERSE(@s)),PATINDEX('%[^0-9]%',

    SUBSTRING(REVERSE(@s),(PATINDEX('%[0-9]%', REVERSE(@s))),LEN(@s)))-1));

    ___________________________
    Do Not Optimize for Exceptions!

  • Thanks Milos...........thanks a lot...you saved my day...

  • That solution works but will give an error if the string consists of only numerals.

    declare @rowstring as varchar(max) = '1251596483'

    select

    case

    when isnumeric(@rowstring) = 1 then @rowstring

    else

    REVERSE(

    SUBSTRING(REVERSE(@rowstring),PATINDEX('%[0-9]%',

    REVERSE(@rowstring)),PATINDEX('%[^0-9]%',

    SUBSTRING(REVERSE(@rowstring),(PATINDEX('%[0-9]%', REVERSE(@rowstring))),LEN(@rowstring)))-1))

    end

    That will (inelegantly) handle all numeral strings if they do occur. I gratefully acknowledge Milos for doing all the hard work.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • BWFC (4/8/2014)


    That solution works but will give an error if the string consists of only numerals.

    Thank you BWFC for the correction!

    A great thing in this forum is whenever you write something which is not completely true or in some corner cases would not work someone puts appropriate comment. That's really great and continuously improve the quality of posts and comments.

    Thanks again!

    ___________________________
    Do Not Optimize for Exceptions!

  • This function will bring back the last number only portion of the string

    😎

    CREATE FUNCTION dbo.udf_GetNumEEric

    (

    @strAlphaNumeric VARCHAR(256)

    )

    RETURNS TABLE

    AS

    RETURN

    WITH NBR1(N) AS (SELECT NM.N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS NM(N))

    ,NUMBERS(N) AS (SELECT TOP (DATALENGTH(@strAlphaNumeric)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM NBR1 N1,NBR1 N2, NBR1 N3)

    ,RAW_STR AS

    ( SELECT

    NM.N

    ,SUBSTRING(@strAlphaNumeric,(LEN(@strAlphaNumeric) + 1 )-NM.N,1) TCHAR

    FROM NUMBERS NM

    )

    ,NUMCHECK AS

    ( SELECT

    RS.N

    ,RS.TCHAR

    ,CASE

    WHEN UNICODE(RS.TCHAR) >= 48 AND UNICODE(RS.TCHAR) <= 57 THEN 0

    ELSE 1

    END AS NM_CHECK

    FROM RAW_STR RS

    )

    ,NUM_PART AS

    ( SELECT

    NC.N

    ,NC.TCHAR

    ,SUM(NC.NM_CHECK) OVER

    ( PARTITION BY (SELECT NULL)

    ORDER BY NC.N ASC

    ROWS BETWEEN UNBOUNDED PRECEDING

    AND CURRENT ROW

    ) AS NM_CHECK

    FROM NUMCHECK NC

    )

    SELECT

    (SELECT

    NP.TCHAR AS [text()]

    FROM NUM_PART NP

    WHERE NP.NM_CHECK = 0

    ORDER BY NP.N DESC

    FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)')

    ;

  • milos.radivojevic (4/8/2014)


    BWFC (4/8/2014)


    That solution works but will give an error if the string consists of only numerals.

    Thank you BWFC for the correction!

    A great thing in this forum is whenever you write something which is not completely true or in some corner cases would not work someone puts appropriate comment. That's really great and continuously improve the quality of posts and comments.

    Thanks again!

    You're welcome and I'd always wondered what the point of the REVERSE function was and now it makes sense. It does help if I myself check for corner cases. My original post didn't work in all cases.

    '0568556506111677862713D8' is considered numeric for some reason and the whole string was returned rather than the final 8.

    This fixes it though.

    declare @rowstring varchar(max)

    SELECT

    case

    when patindex('%[^0-9]%',@rowstring) = 0 then @rowstring

    else

    REVERSE(

    SUBSTRING(REVERSE(@rowstring),PATINDEX('%[0-9]%',

    REVERSE(@rowstring)),PATINDEX('%[^0-9]%',

    SUBSTRING(REVERSE(@rowstring),(PATINDEX('%[0-9]%', REVERSE(@rowstring))),LEN(@rowstring)))-1))

    end


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Underlying code/logic:

    SELECT

    string,

    --len(string), last_numeric_char, length_of_numeric,

    SUBSTRING(string, last_numeric_char - length_of_numeric + 1, length_of_numeric)

    FROM (

    SELECT '123756zxfggr123456' AS string --123456

    UNION ALL

    SELECT '4vbz67xfggr123dfd' --123

    UNION ALL

    SELECT '123756zxfgg43r5' --5

    UNION ALL

    SELECT '123756123456475' --123756123456475

    ) AS test_data

    CROSS APPLY (

    SELECT LEN(string) - PATINDEX('%[0-9]%', REVERSE(string)) + 1 AS last_numeric_char

    ) AS ca1

    CROSS APPLY (

    SELECT PATINDEX('%[^0-9]%', REVERSE(LEFT(string, last_numeric_char - 1)) + '.') AS length_of_numeric

    ) AS ca2

    Coded as function:

    CREATE FUNCTION dbo.udf_GetNumeric

    (@strAlphaNumeric VARCHAR(256))

    RETURNS VARCHAR(256)

    AS

    BEGIN

    RETURN (

    SELECT

    SUBSTRING(string, last_numeric_char - length_of_numeric + 1, length_of_numeric)

    FROM (

    SELECT @strAlphaNumeric AS string

    ) AS param_input

    CROSS APPLY (

    SELECT LEN(string) - PATINDEX('%[0-9]%', REVERSE(string)) + 1 AS last_numeric_char

    ) AS ca1

    CROSS APPLY (

    SELECT PATINDEX('%[^0-9]%', REVERSE(LEFT(string, last_numeric_char - 1)) + '.') AS length_of_numeric

    ) AS ca2

    )

    END

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • For the set-based, tally table, CTE freaks among us, here is a set-based solution that uses a tally table and CTEs:

    SET NOCOUNT ON;

    -- The @Strings table variable represents whatever your source table is.

    DECLARE @Strings TABLE ([StringKey] INT NOT NULL IDENTITY(1, 1),

    [RawString] VARCHAR(1000) NOT NULL,

    PRIMARY KEY CLUSTERED ([StringKey])

    );

    INSERT INTO @Strings ( [RawString] ) VALUES ( '' );

    INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc' );

    INSERT INTO @Strings ( [RawString] ) VALUES ( '789' );

    INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc789' );

    INSERT INTO @Strings ( [RawString] ) VALUES ( '789abc' );

    INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc789abc' );

    INSERT INTO @Strings ( [RawString] ) VALUES ( '123rgt456tgb789asd' );

    INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc123rgt456tgb789' );

    INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc123rgt456tgb789asd' );

    -- we create 6 Common Table Expressions (CTEs)

    -- The first 3 are solely for the purpose of creating a tally table of 1,000 sequential numbers.

    -- If you already have a tally table defined in your database you use it in the [Characters] CTE and remove the first 3 CTEs

    -- The [Characters] CTE breaks each character of each string down into its own row with a value ([IsDigit]) indicating whether it is a numeric character or not

    -- The [LastNumerics] CTE finds the location of the last numeric character in each string

    -- The [[LastNonNumerics]] CTE finds the location of the last non-numeric character in each string that occurs BEFORE the last numeric character

    WITH [First_10]([Dummy]) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), -- first 10 rows

    [Full_1000]([Dummy]) AS (SELECT 1 FROM [First_10] a CROSS JOIN [First_10] b CROSS JOIN [First_10] c), -- 10 x 10 x 10 = 1000 rows

    [Tally_1000]([SeqNo]) AS (SELECT ROW_NUMBER() OVER (ORDER BY [Dummy]) FROM [Full_1000]),

    [Characters] AS (SELECT .[StringKey], [sn].[SeqNo], ISNUMERIC(SUBSTRING(.[RawString], [sn].[SeqNo], 1)) AS [IsDigit]

    FROM @Strings s INNER JOIN [Tally_1000] AS sn ON [sn].[SeqNo] <= LEN(.[RawString])

    WHERE [sn].[SeqNo] <= LEN(.[RawString])

    ),

    [LastNumerics] AS (SELECT [c].[StringKey], MAX([c].[SeqNo]) AS [LastNumeric]

    FROM [Characters] AS c

    WHERE [c].[IsDigit] = 1

    GROUP BY [c].[StringKey]

    ),

    [LastNonNumerics] AS (SELECT [c].[StringKey], MAX([c].[SeqNo]) AS [LastNonNumeric]

    FROM [Characters] AS c INNER JOIN [LastNumerics] n ON [n].[StringKey] = [c].[StringKey]

    AND [c].[SeqNo] < [n].[LastNumeric]

    WHERE [c].[IsDigit] = 0

    GROUP BY [c].[StringKey]

    )

    -- Now we can do our SELECT query to extract the characters from each string starting right after the last non-numeric character and going through the last numeric character.

    SELECT .[RawString], SUBSTRING(.[RawString], ISNULL([nn].[LastNonNumeric], -1) + 1, ISNULL([n].[LastNumeric], 0) - ISNULL([nn].[LastNonNumeric], -1)) AS [Result]

    FROM @Strings s LEFT OUTER JOIN [LastNumerics] n ON [n].[StringKey] = .[StringKey]

    LEFT OUTER JOIN [LastNonNumerics] nn ON [nn].[StringKey] = .[StringKey];

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply