Pick a certain number from a string in select

Question

I need to take a certain number that is inside a string (in case the return of a select). In the table is saved like this:

CreditCash_Aderencia_AD_20180823;20180823;Arquivo convertido para CSV com 503 linhas
CreditCash_Aderencia_20180823;20180823;Arquivo convertido para CSV com 1399 linhas
CreditCash_Aderencia;20180823;Não foi possível executar o sistema em 3 tentativas
CreditCash_Aderencia_AD_20180822;20180822;Arquivo convertido para CSV com 500 linhas

In this string I need to get only the number that is before linhas, in the case of 503 for example (which can be variable).

I need that number 'cause I’m gonna put it in a update to make changes only to those that have more than 500 converted lines.

Answer 1

The code below allows you to achieve what you want:

DECLARE @strText    NVARCHAR(MAX)
DECLARE @intAlpha   INT

SET @strText = 'CreditCash_Aderencia_AD_20180823;20180823;Arquivo convertido para CSV com 503 linhas'
SET @strText = RIGHT(@strText, CHARINDEX(';', REVERSE(@strText) + ';') - 1)

SET @intAlpha = PATINDEX('%[^0-9]%', @strText)

BEGIN
    WHILE @intAlpha > 0
    BEGIN
        SET @strText    = STUFF(@strText, @intAlpha, 1, '')
        SET @intAlpha   = PATINDEX('%[^0-9]%', @strText)
    END
END

SELECT ISNULL(@strText, 0)

You can always adapt this to a function to use in queries:

CREATE FUNCTION GetOnlyNumber (@Text NVARCHAR(MAX))
RETURNS INT
AS
BEGIN
    DECLARE @intAlpha   INT

    SET @Text = RIGHT(@Text, CHARINDEX(';', REVERSE(@Text) + ';') - 1)

    SET @intAlpha = PATINDEX('%[^0-9]%', @Text)

    BEGIN
        WHILE @intAlpha > 0
        BEGIN
            SET @Text       = STUFF(@Text, @intAlpha, 1, '')
            SET @intAlpha   = PATINDEX('%[^0-9]%', @Text)
        END
    END

    RETURN ISNULL(@Text, 0)
END