Get a certain number from a string in select

Question

Get a certain number from a string in select

Navigation

#1 by (2 votes)

1

I need to get a certain number that is inside a string (in this case the return of a select ). The table is written 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 case 503 for example (which can be variable).

I need this number because I will put in update to make changes only in those that have more than 500 converted lines.

sql

asked by anonymous 24.08.2018 / 19:16

1 answer

Remove elements from the end of a list in C Pick up mouse position within scroll pane

score 2 · Accepted Answer

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