Início / dba / Perguntas / 221642
Accepted
MrVimes
Asked: 2018-11-03 05:43:17 +0800 CST

Tentando verificar se uma string contém um número exclusivamente

  • 772

Eu tenho tentado escrever uma função para verificar se uma string contém um número sem que esse número faça parte de um número maior (em outras palavras, se o número pesquisado for '6' e a string for '7+16+2' deve retornar false, porque o '6' nesta string faz parte do número '16')

Eu escrevi a função abaixo (é longa, mas eu ia testá-la antes de refatorá-la)

Ao testar, encontrei um bug pelo qual ele executa apenas a primeira instância de um número encontrado através da lógica. Portanto, executar esta função com '6' contra '16+7+9+6' retornaria false porque determina que o primeiro '6' faz parte de um número maior e interrompe o processamento.

Achei que para contornar isso eu tenho que implementar um loop para encurtar a string 'haystack' (para que, usando o exemplo '16+7+9+6' a função continue verificando '+7+9+6' após eliminar o primeiro '6'), mas antes de gastar tempo para fazer uma função já complicada ainda mais complicada eu queria verificar se existe uma maneira mais simples de atingir o mesmo objetivo?

drop function dbo.runners_contain_runner
go
create function dbo.runners_contain_runner(@runner varchar(max), @runners varchar(max))
returns int
as 
begin

    /*
    eliminate the plus sign from @runners so that the 
    'isnumeric' function doesn't return false positives (it returns 1 for '+') 
    */
    set @runners = replace(@runners,'+','_' ) 


    declare @ret int;
    set @ret = 0;

    -- if the runner is the only runner return 1
    if @runners = @runner 
        set @ret = 1
    else
    begin
        declare @charindex int;
        set @charindex = charindex(@runner,@runners)
        if @charindex > 0
        begin

            -- if it is at the beginning then check the char after it 
            if @charindex = 1 
            begin
                if isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0
                    set @ret = @charindex
            end

            -- if it is at the end then check the char before it
            else if @charindex = len(@runners) - (len(@runner) - 1) 
            begin
                if isnumeric(substring(@runners,@charindex - 1,1)) = 0
                    set @ret = @charindex
            end

            -- if it is in the middle check the chars either side of it
            else
            begin
                if isnumeric(substring(@runners,@charindex - 1,1)) + 
                isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0
                    set @ret = @charindex 
            end
        end
    end
    return @ret
end
sql-server t-sql

2 respostas

  1. Best Answer

    Talvez você esteja complicando demais isso, concentrando-se demais em querer um número. Dê um passo para trás por um momento. O que você realmente quer é uma substring sem nenhum dígito em ambos os lados. A única maneira de um número fazer parte de um número maior é ter pelo menos 1 dígito em cada lado dele, certo? Portanto, desde que você passe apenas números, essa definição ainda deve produzir números que não possuem dígitos em nenhum dos lados.

    Com isso em mente, precisamos apenas de 3 PATINDEXpredicados para cobrir o valor passado na extrema esquerda, na extrema direita ou no meio. Tente o seguinte, pois parece funcionar:

    GO
CREATE PROCEDURE #TestFindRunner
(
  @Runner VARCHAR(10)
)
AS
SET NOCOUNT ON;

DECLARE @Data TABLE
(
  [ID] INT NOT NULL PRIMARY KEY,
  [Runners] VARCHAR(50) NULL
 );

INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6');
INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45');
INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92');
INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6');
INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63');
INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL);
INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6');

SELECT tmp.*
FROM   @Data tmp
WHERE  @Runner COLLATE Latin1_General_100_BIN2 = tmp.[Runners]
OR     PATINDEX('%[^0123456789]' + @Runner COLLATE Latin1_General_100_BIN2,
                tmp.[Runners]) > 0
OR     PATINDEX(@Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2,
                tmp.[Runners]) > 0
OR     PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%'
                COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0
GO

    E depois teste com:

    EXEC #TestFindRunner 0;
EXEC #TestFindRunner 2;
EXEC #TestFindRunner 4;
EXEC #TestFindRunner 8;
EXEC #TestFindRunner 11;
-- 0 rows

EXEC #TestFindRunner 3;   -- 4
EXEC #TestFindRunner 77;  -- 3
EXEC #TestFindRunner 111; -- 5
-- 1 row

EXEC #TestFindRunner 5; -- 2 and 3
-- 2 rows

EXEC #TestFindRunner 1; -- 6, 7, 8, and 9
-- 4 rows

EXEC #TestFindRunner 6; -- 1, 4, 6, 7, and 11
-- 5 rows

EXEC #TestFindRunner 7; -- 1, 2, 6, 7, 8, and 9
-- 6 rows

EXEC #TestFindRunner 9; -- 1, 2, 3, 6, 7, 8, and 9
-- 7 rows

    O motivo de ter 3 variações do PATINDEXé que PATINDEXos padrões de busca não são Expressões Regulares (RegeEx), ao contrário do que muitas pessoas dizem/pensam (mesmo com LIKEos padrões). PATINDEXe LIKEos padrões não possuem quantificadores, portanto não é possível especificar que a [^0123456789]substituição de um único caractere deve ser "0 ou mais"; é "um e apenas um; nem mais, nem menos".

    Forçar o agrupamento binário (ou seja, COLLATE Latin1_General_100_BIN2após cada @Runnerreferência) garante que estamos lidando apenas com esses 10 dígitos decimais e não com qualquer outro caractere que possa ser considerado equivalente

    Para colocar a lógica acima em uma função de valor de tabela em linha (TVF) para que seja mais fácil de usar (e mais eficiente do que uma UDF escalar igualmente fácil de usar), tente o seguinte:

    USE [tempdb];
GO
CREATE FUNCTION dbo.IsRunnerPresent
(
  @Runner VARCHAR(10),
  @Runners VARCHAR(8000)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

  SELECT CONVERT(BIT,
    CASE WHEN @Runner COLLATE Latin1_General_100_BIN2 = @Runners
           OR PATINDEX('%[^0123456789]' + @Runner
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           OR PATINDEX(@Runner + '[^0123456789]%'
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           OR PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%'
                       COLLATE Latin1_General_100_BIN2, @Runners) > 0
           THEN 1
         ELSE 0
      END) AS [RunnerFound];
GO

    E depois teste com:

    DECLARE @Runner VARCHAR(10);
SET @Runner = '6';


DECLARE @Data TABLE
(
  [ID] INT NOT NULL PRIMARY KEY,
  [Runners] VARCHAR(50) NULL
 );

INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6');
INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5');
INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45');
INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92');
INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6');
INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63');
INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7');
INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL);
INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6');

SELECT tmp.[ID],
       tmp.[Runners],
       fnd.[RunnerFound]
FROM   @Data tmp
CROSS APPLY dbo.IsRunnerPresentTVF(@Runner, tmp.[Runners]) fnd;

    Que retorna:

    ID    Runners          RunnerFound
1     16+7+9+6         1
2     16+7+9+5         0
3     26+77+9+5        0
4     6+3+45           1
5     63,808,111,92    0
6     1-7-9,6          1
7     1-6-9,7          1
8     1-7-9,63         0
9     1-63-9,7         0
10    NULL             0
11    6                1
    • 5

  2. Desde então, reescrevi minha função da seguinte maneira.

    Edit : completei isso antes de ver a resposta de Solomon Rutzky à minha pergunta. Sua resposta funciona e é melhor que a minha. Mas vou deixar esta resposta como alternativa.

    drop function dbo.runners_contain_runner
go
create function dbo.runners_contain_runner(@runner varchar(max), @runners varchar(max))
returns int
as 
begin
    declare @ret int

    if @runner = @runners
        set @ret = 1
    else if charindex(@runner,@runners) = 0
        set @ret = 0
    else
    begin
        declare @foundindex int
        declare @item varchar(max)
        declare @tmp varchar(max)

        declare @position int
        set @tmp = @runners
        set @position = 1
        set @foundindex = 0

        while @position > 0
        begin
            set @foundindex = @foundindex + @position 
            set @position = charindex('+',@tmp)

            if @position > 0
            begin
                set @item = substring(@tmp,1,@position - 1)
                set @tmp = substring(@tmp,@position + 1,len(@tmp) - @position)
            end
            else
                set @item = @tmp
            if @item = @runner
            begin
                set @ret = @foundindex
                break
            end
            else
                set @ret = 0
        end
    end
    return @ret
end
    • 0

relate perguntas