Código de substituição de cadeia de caracteres do SQL Server com STRING_SPLIT

Código de substituição de cadeia de caracteres do SQL Server com STRING_SPLIT

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Por: Aaron Bertrand | Atualizado: 2020-06-01 | Comentários | Relacionados: Mais> Funções – Sistema

Problema

O SQL Server 2016 introduziu uma nova função interna, STRING_SPLIT, muito mais eficiente
do que as funções que temos nos implementado todos esses anos. Enquanto eles
Para atualizar para versões mais recentes do SQL Server, um projeto que muitas lojas seguirão
on está substituindo todas essas funções ineficientes por chamadas diretas (ou indiretas)
para STRING_SPLIT. Infelizmente, existem algumas limitações conhecidas que podem impedir
isso é uma troca fácil, um por um. Eu queria compartilhar algumas maneiras de trabalhar
em torno desses problemas para ter uma implantação bem-sucedida.

Solução

A mudança para a nova função mais eficiente certamente parece direta:
substituir todas as chamadas para dbo.YourTableValuedSplitFunction com chamadas para
STRING_SPLIT.
Mas pode haver algumas barreiras significativas impedindo que você altere isso diretamente,
Incluindo:

Acesso à Consulta

  • Talvez você não consiga alterar as consultas
    diretamente devido a políticas de controle de origem ou porque estão em aplicativos externos
    código que não pode ser implantado imediatamente (ou bloqueado por um fornecedor).
  • Nesses casos, sua única opção é deixar a função existente no lugar,
    mas mude seu corpo diretamente.

Nível de compatibilidade

  • Este é um que pega as pessoas por
    surpresa: embora o nível de compatibilidade seja irrelevante para a maioria das novas sintaxes no SQL
    Servidor, esta função específica possui um requisito mínimo de 130. Seu banco de dados
    pode estar em um nível de compatibilidade mais baixo por vários motivos.
  • Nisso
    Nesse caso, a única maneira de usar
    STRING_SPLIT é executá-lo em um banco de dados
    com nível compat 130.

Comportamento Diferente

  • Existem várias maneiras de a função nativa
    podem diferir das funções que você criou:
  1. A nova função possui uma única coluna de saída, value. Suas consultas existentes
    pode usar um nome de coluna diferente para os itens individuais que saem e
    também pode confiar em outras colunas de saída, como um índice ou posição ordinal.
  2. o STRING_SPLIT função não elimina duplicatas, retorna um tipo de dados diferente
    que strings, ou garantir que os valores serão retornados no mesmo
    ordem em que aparecem na lista.
  3. Sua função pode lidar com delimitadores iniciais ou finais, valores NULL,
    ou espaço em branco de maneira diferente e pode aceitar delimitadores com vários caracteres,
    qual a função nativa não.
  • Nesses casos, você precisará criar um wrapper em torno de STRING_SPLIT

    para obter esses comportamentos.

Resolvendo o problema de acesso à consulta

Contanto que você possa alterar a função em si, não é tão importante
se você for impedido de alterar consultas individuais que a chamam. Vamos
digamos que você tenha esta função com valor de tabela com várias instruções:

CREATE FUNCTION dbo.SimpleSplitFunction
(
  @List      nvarchar(max),
  @Delimiter nchar(1)
)
RETURNS @t table (Item nvarchar(max))
AS
BEGIN
  SET @List += @Delimiter;   ;WITH a(f,t) AS 
  (
    SELECT CAST(1 AS bigint), CHARINDEX(@Delimiter, @List)
    UNION ALL
    SELECT t + 1, CHARINDEX(@Delimiter, @List, t + 1)
    FROM a WHERE CHARINDEX(@Delimiter, @List, t + 1) >
  ) 
  INSERT @t SELECT SUBSTRING(@List, f, t - f) FROM a OPTION (MAXRECURSION );   RETURN
END
GO

E há uma consulta como esta, em algum lugar, que você não pode mudar:

SELECT Item FROM dbo.SimpleSplitFunction(N'foo,bar', N',');			

Você pode implementar STRING_SPLIT alterando a função da seguinte maneira:

ALTER FUNCTION dbo.SimpleSplitFunction
(
  @List      nvarchar(max),
  @Delimiter nchar(1)
)
RETURNS @t table (Item nvarchar(max))
AS
BEGIN
  INSERT @t(Item) SELECT value FROM STRING_SPLIT(@List, @Delimiter);
  RETURN;
END
GO

Ainda melhor, porém, seria alterá-lo para uma função com valor de tabela embutido
(que requer descartar / criar):

DROP FUNCTION dbo.SimpleSplitFunction;
GO CREATE FUNCTION dbo.SimpleSplitFunction
(
  @List      nvarchar(max),
  @Delimiter nchar(1)
)
RETURNS TABLE
AS
  RETURN (SELECT Item = value FROM STRING_SPLIT(@List, @Delimiter));
GO

Nos dois casos, o código que atualmente chama essa função não precisa
mudar de todo.

Resolvendo o problema do nível de compatibilidade

Temos vários bancos de dados em níveis de compatibilidade mais antigos, portanto, alterar consultas
em execução no contexto desses bancos de dados para usar STRING_SPLIT não é possível. E se
Se você tentar criar ou alterar uma função em um banco de dados de nível de compatibilidade inferior,
encontre imediatamente um erro de tempo de compilação:

CREATE DATABASE CompatLevel_120;
GO ALTER DATABASE CompatLevel_120 SET COMPATIBILITY_LEVEL = 120;
GO USE CompatLevel_120;
GO CREATE FUNCTION dbo.SimpleSplitFunction
(
  @List      nvarchar(max),
  @Delimiter nchar(1)
)
RETURNS TABLE
AS
  RETURN (SELECT Item=value FROM STRING_SPLIT(@List, @Delimiter));
GO

Mensagem de erro:

Msg 208, Level 16, State 1, Procedure SimpleSplitFunction, Line 8
Invalid object name 'STRING_SPLIT'.

Supondo que exista um banco de dados na instância que esteja no nível compatível 130 ou
que você pode criar esse banco de dados, existem algumas maneiras de continuar usando
STRING_SPLIT nesse banco de dados. Primeiro, crie a função acima (ou sua variação)
em, digamos, o Utility base de dados. Em seguida, altere a função no original
banco de dados da seguinte maneira, para que ele atue como um relé:

USE CompatLevel_120;
GO CREATE FUNCTION dbo.SimpleSplitFunction
(
  @List      nvarchar(max),
  @Delimiter nchar(1)
)
RETURNS TABLE
AS
  RETURN (SELECT Item FROM Utility.dbo.SimpleSplitFunction(@List, @Delimiter));
GO

Ou largue a função original e adicione um sinônimo, que é um pouco mais simples
maneira de realizar a mesma coisa:

USE CompatLevel_120;
GO CREATE SYNONYM dbo.SimpleSplitFunction FOR Utility.dbo.SimpleSplitFunction;

Nos dois casos, o código de chamada não precisa ser alterado até que você
são capazes de aumentar o nível de compatibilidade e substituir essas referências de função
nas consultas com mais simples STRING_SPLIT chamadas.

Resolvendo problemas de comportamento

Listei várias mudanças de comportamento acima que você pode ter codificado em seu próprio
funções, mas não são possíveis com o STRING_SPLIT nativo. Um bom compromisso
está mudando suas funções para usar STRING_SPLIT enquanto ainda apoia aqueles adicionais
recursos, alguns dos quais são mais fáceis de implementar do que outros.

Saída com um nome de coluna diferente

Este é um tipo de softball, e já demonstrado acima. E se value não é
o nome certo para a coluna de saída, sua função pode apenas dizer:

RETURN (SELECT YourColumnName = value FROM STRING_SPLIT(@List, @Delimiter));			

Eliminar duplicatas

A eliminação de duplicatas é outro recurso comum das funções de divisão e
isso pode ser realizado com STRING_SPLIT simplesmente usando GROUP BY:

RETURN (SELECT YourColumnName = value FROM STRING_SPLIT(@List, @Delimiter) GROUP BY value);			

Eu olho para isso com mais detalhes em uma dica anterior (escrita antes
STRING_SPLIT), “Removendo
Duplicatas de Strings no SQL Server. “

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

Saída de uma coluna de pedidos

Um aprimoramento bastante comum é indicar a localização de uma sequência individual
valor da lista original (eles podem ser listados por prioridade). Se você não
esperar duplicatas (ou apenas se preocupam com a localização do primeiro duplicado),
você pode simplesmente usar CHARINDEX:

RETURN
  (
    SELECT value, ListPos = ROW_NUMBER() OVER (ORDER BY pointer)
    FROM
    (
      SELECT value,
             pointer = CHARINDEX(@Delimiter + value + @Delimiter,
                                 @Delimiter + @List + @Delimiter)
      FROM STRING_SPLIT(@List, @Delimiter)
    ) AS s
  );

A consulta externa pode então ORDER BY ListPos ou, se você estiver tentando encontrar
entãoº elemento, WHERE ListPos = n. Você também pode adicionar
pointer ao
saída para determinar a que distância da cadeia uma determinada entidade aparece.

Se você tiver duplicatas (e não as eliminará primeiro),
Eu investigo algumas outras abordagens em uma dica anterior, “Resolver
problemas antigos com as novas funções STRING_AGG e STRING_SPLIT do SQL Server. ”

Saída de um tipo de dados diferente

Se você precisar gerar um tipo específico, basta aplicar CONVERT(value) ou
TRY_CONVERT(value) antes de retornar, verifique se o código de chamada está preparado
para lidar com quaisquer erros (ou lidar com NULL)

RETURN (SELECT value = TRY_CONVERT(int, value) FROM STRING_SPLIT(@List, @Delimiter));			

Ignorar delimitadores iniciais / finais

Algumas strings inevitavelmente entram com elementos vazios no início ou
fim. O seguinte retorna 5 linhas, com a primeira e a última sendo cadeias vazias:

SELECT value FROM STRING_SPLIT(N',foo,bar,blat,',N',');			

Se você deseja garantir apenas os valores que não são cordas vazias
são retornados:

RETURN (SELECT value FROM STRING_SPLIT(@List, @Delimiter) WHERE RTRIM (value) > N'');			

No entanto, se você estiver bem com cadeias vazias em qualquer lugar exceto a
início e fim da lista, fica um pouco mais complicado. No SQL Server
2017 e acima, você pode usar o TRIM para remover primeiro qualquer delimitador inicial / final.
Isso retorna 3 linhas:

SELECT * FROM STRING_SPLIT(TRIM(N',' FROM N',foo,bar,blat,'),N',');			

Na sua função, isso é simplesmente:

RETURN (SELECT value FROM STRING_SPLIT(TRIM(@Delimiter FROM @List), @Delimiter));			

Nas versões mais antigas, é muito mais confuso (e confesso que não
gastar muito tempo tornando isso mais conciso – provavelmente há
caminhos):

RETURN
  (
   WITH l(l,d)AS (SELECT LTRIM(RTRIM(@List)), @Delimiter),
    x(l,d) AS (SELECT STUFF(l,1, CASE WHEN LEFT(l,1)= d THEN 1 ELSE 0 END, N''), d FROM l),
    y(l,d) AS (SELECT LEFT(l,LEN(l) - CASE WHEN RIGHT(l,1)= d THEN 1 ELSE 0 END), d FROM x)
   SELECT s.value FROM y CROSS APPLY STRING_SPLIT(y.l, y.d) AS s
  );

Ignorar espaço em branco

Todos os tipos de caracteres de espaço em branco podem aparecer antes ou, mais comumente, depois
uma linha. STRING_SPLIT tratará um espaço ou guia como um elemento válido, portanto, o seguinte
retorna 4 linhas, mesmo que estes sejam elementos indiscutivelmente inválidos:

SELECT * FROM STRING_SPLIT(CHAR(9) + ',' + CHAR(13) + ',' + CHAR(10) + ',' + CHAR(32),',');			

Se você deseja que esses itens sejam eliminados da produção, em 2017 e acima, você pode
novamente use TRIM para remover retornos de carro, avanços de linha, espaços e guias da
início e fim da lista ou de qualquer elemento.

RETURN
  (
   SELECT value FROM
    (
     SELECT TRIM(CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) FROM value)
     FROM STRING_SPLIT
      (
       TRIM(CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) FROM @List),
        @Delimiter
      )
    )AS y (value) WHERE value > N''
  );

Nas versões mais antigas, novamente, isso envolverá uma solução confusa, muito mais confusa do que
um único delimitador inicial ou final (porque o que acontece se uma sequência começa
com 12 abas e termina com 47 retornos de carro?). E eu não posso prever
todos os casos de uso disponíveis – você pode tratar uma guia ou um feed de linha como
um elemento válido ou apenas os ignore ou aceite quando forem o primeiro ou o último
elemento. Então, deixarei isso como um exercício para o leitor (por enquanto).

Manipular delimitadores de vários caracteres

Infelizmente, STRING_SPLIT só pode manipular delimitadores com um caractere e
às vezes isso não é suficiente. Eu mostro algumas maneiras pelas quais você pode lidar com essa limitação
em uma dica anterior, “Lidando
com o delimitador de caractere único na função STRING_SPLIT do SQL Server. ”

Conclusão

STRING_SPLIT é uma adição fantástica às versões modernas do SQL Server, mas em
de certa forma, é inferior às soluções que você implementou enquanto esperava
para uma solução nativa. Com algumas das sugestões listadas aqui, espero
você ainda pode tirar vantagem, mesmo que envolva fumaça temporária e
espelhos.

Próximos passos

Continue lendo para obter dicas relacionadas e outros recursos que envolvem a divisão de cadeias:

Última atualização: 2020-06-01

Sobre o autor

Aaron Bertrand (@AaronBertrand) é um tecnólogo apaixonado com experiência no setor que remonta ao ASP clássico e SQL Server 6.5. Ele é editor-chefe do blog relacionado ao desempenho, SQLPerformance.com, e também blogs no sqlblog.org.

Ver todas as minhas dicas



cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
Leia Também  Introdução ao utilitário SQLPackage