STRING_SPLIT (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPonto de extremidade de análises SQL no Microsoft FabricWarehouse no Microsoft Fabric

STRING_SPLIT é uma função com valor de tabela que divide uma cadeia de caracteres em linhas de subcadeias de caracteres com base em um caractere separador especificado.

Nível de compatibilidade 130

STRING_SPLIT requer que o nível de compatibilidade seja, no mínimo, 130. Quando o nível de compatibilidade é inferior a 130, o Mecanismo de Banco de Dados não consegue localizar a função STRING_SPLIT.

Para alterar o nível de compatibilidade de um banco de dados, consulte Exibir ou alterar o nível de compatibilidade de um banco de dados.

Observação

A configuração de compatibilidade não é necessária para STRING_SPLIT no Azure Synapse Analytics.

Convenções de sintaxe de Transact-SQL

Sintaxe

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Argumentos

cadeia de caracteres

Uma expression de qualquer tipo de caractere (ou seja, nvarchar, varchar, nchar ou char).

separator

Uma expressão de caractere único de qualquer tipo de caractere (por exemplo, nvarchar(1), varchar(1), nchar(1) ou char(1)) usada como separador de subcadeias de caracteres concatenadas.

enable_ordinal

Aplica-se a: Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure, Azure Synapse Analytics (somente pool de SQL sem servidor) e SQL Server 2022 (16.x) e versões posteriores.

Uma expressãoint ou bit que serve como um sinalizador para habilitar ou desabilitar a coluna de saída ordinal. O valor 1 habilita a coluna ordinal. Se enable_ordinal for omitido, NULL ou tiver um valor 0, a coluna ordinal será desabilitada.

Tipos de retorno

Se a coluna de saída ordinal não estiver habilitada, STRING_SPLIT retornará uma tabela de coluna única cujas linhas são as subcadeias de caracteres. O nome da coluna é value. Ele retornará nvarchar se um dos argumentos de entrada for nvarchar ou nchar. Caso contrário, ele retornará varchar. O tamanho do tipo de retorno é o mesmo que o tamanho do argumento da cadeia de caracteres.

Se o argumento enable_ordinal for passado como um valor 1, uma segunda coluna chamada ordinal será retornada, que consiste nos valores de índice com base em 1 da posição de cada subcadeia de caracteres na cadeia de caracteres de entrada. O tipo de retorno é bigint.

Comentários

STRING_SPLIT insere uma cadeia de caracteres que tem subcadeias de caracteres delimitadas e insere um caractere a ser usado como separador ou delimitador. Opcionalmente, a função dá suporte a um terceiro argumento com valor 0 ou 1 que desabilita ou habilita, respectivamente, a coluna de saída ordinal.

STRING_SPLIT gera uma tabela com coluna única ou coluna dupla, dependendo do argumento enable_ordinal.

  • Se enable_ordinal for NULL, omitido ou tiver o valor 0, STRING_SPLIT retornará uma tabela de coluna única cujas linhas contêm as subcadeias de caracteres. O nome da coluna de saída é value.

  • Se enable_ordinal tiver o valor 1, a função retornará uma tabela de duas colunas, incluindo a coluna ordinal que consiste nos valores de índice com base em 1 das subcadeias de caracteres na cadeia de caracteres de entrada original.

O argumento enable_ordinal deve ser um valor constante, não uma coluna ou variável. Ele deve ser um tipo de dado bit ou int com valor 0 ou 1. Caso contrário, a função gerará um erro.

As linhas de saída podem estar em outra ordem. A ordem não é a garantia de corresponder à ordem das subcadeias de caracteres na cadeia de caracteres de entrada. É possível substituir a ordem de classificação final usando uma cláusula ORDER BY na instrução SELECT, por exemplo, ORDER BY value ou ORDER BY ordinal.

0x0000 (char(0)) é um caractere indefinido em ordenações do Windows e não pode ser incluído em STRING_SPLIT.

Subcadeias de caracteres de comprimento zero vazias estão presentes quando a cadeia de caracteres de entrada contém duas ou mais ocorrências consecutivas do caractere delimitador. As subcadeias de caracteres vazias são tratadas da mesma forma que são as subcadeias de caracteres sem formatação. É possível filtrar as linhas que contêm a subcadeia de caracteres vazia usando a cláusula WHERE, por exemplo, WHERE value <> ''. Se a cadeia de caracteres de entrada for NULL, a função com valor de tabela STRING_SPLIT retornará uma tabela vazia.

Por exemplo, a seguinte instrução SELECT usa o caractere de espaço como o separador:

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

Na prática, a instrução SELECT anterior retornava a seguinte tabela de resultado:

value
Lorem
ipsum
dolor
sit
amet.

O exemplo a seguir habilita a coluna ordinal passando 1 para o terceiro argumento opcional:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

Essa instrução retornará a seguinte tabela de resultados:

value ordinal
Lorem 1
ipsum 2
dolor 3
sit 4
amet. 5

Exemplos

a. Dividir uma cadeia de caracteres de valores separados por vírgula

Analise uma lista separada por vírgulas de valores e retorne todos os tokens não vazios:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'

SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';

STRING_SPLIT retornará a cadeia de caracteres vazia se não houver nada entre o separador. A condição RTRIM(value) <> '' remove tokens vazios.

B. Dividir uma cadeia de caracteres de valores separados por vírgula em uma coluna

A tabela Product tem uma coluna com uma lista separada por vírgula de marcas mostradas no seguinte exemplo:

ProductId Nome Marcas
1 Full-Finger Gloves clothing,road,touring,bike
2 LL Headset bike
3 HL Mountain Frame bike,mountain

A seguinte consulta transforma cada lista de marcas e une-as com a linha original:

SELECT ProductId, Name, value
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',');

Este é o conjunto de resultados.

ProductId Nome Valor
1 Full-Finger Gloves clothing
1 Full-Finger Gloves road
1 Full-Finger Gloves touring
1 Full-Finger Gloves bike
2 LL Headset bike
3 HL Mountain Frame bike
3 HL Mountain Frame mountain

Observação

A ordem da saída pode variar, uma vez que não há garantia de que a ordem corresponda à ordem das subcadeias de caracteres na cadeia de entrada.

C. Agregação por valores

Os usuários precisam criar um relatório que mostra o número de produtos por marca, ordenado pelo número de produtos, e filtrar apenas as marcas com mais de dois produtos.

SELECT value as tag, COUNT(*) AS [number_of_articles]
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',')
GROUP BY value
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC;

D. Pesquisar por valor de marca

Os desenvolvedores precisam criar consultas que localizam artigos por palavras-chave. Eles podem usar as seguintes consultas:

Para localizar produtos com uma única marca (clothing):

SELECT ProductId, Name, Tags
FROM Product
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));

Localize produtos com duas marcas especificadas (clothing e road):

SELECT ProductId, Name, Tags
FROM Product
WHERE EXISTS (SELECT *
    FROM STRING_SPLIT(Tags, ',')
    WHERE value IN ('clothing', 'road'));

E. Localizar linhas pela lista de valores

Os desenvolvedores precisam criar uma consulta que localiza artigos por uma lista de IDs. Eles podem usar a seguinte consulta:

SELECT ProductId, Name, Tags
FROM Product
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

O uso de STRING_SPLIT anterior é uma substituição para um antipadrão comum. Esse antipadrão pode envolver a criação de uma cadeia de caracteres SQL dinâmica na camada de aplicativo ou no Transact-SQL. Ou um antipadrão pode ser obtido usando o operador LIKE. Confira a seguinte instrução SELECT de exemplo:

SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

F. Encontrar linhas por valores ordinais

A seguinte instrução localiza todas as linhas com um valor par de índices:

SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;

A instrução acima retorna a seguinte tabela:

value ordinal
Texas 2
Washington 4
Colorado 6

G. Classificar linhas por valores ordinais

A seguinte instrução retorna os valores de subcadeias de caracteres divididos da cadeia de caracteres de entrada e os valores ordinais delas, classificados pela coluna ordinal:

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;

A instrução acima retorna a seguinte tabela:

value ordinal
Um 5
B 4
C 3
D 2
E 1

Conteúdo relacionado