STRING_SPLIT (Transact-SQL)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse em Microsoft Fabric SQL database in Microsoft Fabric

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

Nível de compatibilidade 130

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

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 o Azure Synapse Analytics.

Transact-SQL convenções de sintaxe

Sintaxe

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Arguments

string

Uma expressão de qualquer tipo de caractere (por exemplo, nvarchar, varchar, nchar ou char).

separador

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

enable_ordinal

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

Uma expressão int ou bit que serve como um sinalizador para habilitar ou desabilitar a ordinal coluna de saída. Um valor de 1 habilita a ordinal coluna. Se enable_ordinal for omitida NULLou tiver um valor de , a 0 coluna será desativadaordinal.

Tipos de devolução

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

Se o argumento enable_ordinal for passado um valor de , uma segunda coluna nomeada 1 será retornada que consiste nos valores de índice baseados em 1 da posição de cada substring na cadeia de ordinalentrada. O tipo de retorno é bigint.

Observações

STRING_SPLIT insere uma cadeia de caracteres que tem substrings delimitadas e insere um caractere para usar como delimitador ou separador. Opcionalmente, a função suporta um terceiro argumento com um valor de ou 0 que desativa ou habilita, respectivamente, a 1 coluna de ordinal saída.

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

Se enable_ordinal for NULL, omitido ou tiver um valor de , 0 retornará uma tabela de STRING_SPLITcoluna única cujas linhas contêm as substrings. O nome da coluna de saída é value.
Se enable_ordinal tiver um valor de , a função retornará uma tabela de duas colunas, incluindo a 1 coluna que consiste nos valores de índice baseados em 1 das substrings na cadeia de ordinalcaracteres de entrada original.

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

As linhas de saída podem estar em qualquer ordem. Não é garantido que a ordem corresponda à ordem das substrings na cadeia de caracteres de entrada. Você pode substituir a ordem de classificação final usando uma ORDER BY cláusula na SELECT instrução, por exemplo, ORDER BY value ou ORDER BY ordinal.

0x0000 (char(0)) é um caractere indefinido em agrupamentos do Windows e não pode ser incluído no STRING_SPLIT.

Substrings vazias de comprimento zero estão presentes quando a cadeia de entrada contém duas ou mais ocorrências consecutivas do caractere delimitador. As substrings vazias são tratadas da mesma forma que as substrings simples. Você pode filtrar quaisquer linhas que contenham a substring vazia usando a WHERE cláusula, por exemplo WHERE value <> ''. Se a cadeia de caracteres de entrada for NULL, a função com valor de STRING_SPLIT tabela retornará uma tabela vazia.

Como exemplo, a instrução a seguir SELECT usa o caractere de espaço como separador:

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

Em uma execução prática, o anterior SELECT retornou a seguinte tabela de resultados:

valor
`Lorem`
`ipsum`
`dolor`
`sit`
`amet.`

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

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

Em seguida, esta instrução retorna a seguinte tabela de resultados:

valor	Ordinal
`Lorem`	1
`ipsum`	2
`dolor`	3
`sit`	4
`amet.`	5

Examples

A. Cadeia de valores separada por vírgulas dividida

Analise uma lista de valores separados por vírgulas 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 retorna uma cadeia de caracteres vazia se não houver nada entre o separador. A condição RTRIM(value) <> '' remove tokens vazios.

B. Dividir a cadeia de valores separados por vírgulas em uma coluna

A tabela de produtos tem uma coluna com uma lista separada por vírgulas de tags mostradas no exemplo a seguir:

ID do Produto	Nome	Etiquetas
`1`	`Full-Finger Gloves`	`clothing,road,touring,bike`
`2`	`LL Headset`	`bike`
`3`	`HL Mountain Frame`	`bike,mountain`

A consulta a seguir transforma cada lista de tags e as une à linha original:

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

Aqui está o conjunto de resultados.

ID do Produto	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, pois não é garantido que a ordem corresponda à ordem das substrings na cadeia de entrada.

C. Agregação por valores

Os usuários devem criar um relatório que mostre o número de produtos por cada tag, ordenados por número de produtos, e filtrar apenas as tags 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 tag

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

Para encontrar produtos com uma única etiqueta (vestuário):

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

Encontre produtos com duas etiquetas especificadas (vestuário e estrada):

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

E. Localizar linhas por lista de valores

Os desenvolvedores devem criar uma consulta que encontre 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 anterior STRING_SPLIT é um substituto 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 alcançado usando o LIKE operador. Veja a seguinte instrução de exemplo SELECT :

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

F. Localizar linhas por valores ordinais

A instrução a seguir localiza todas as linhas com um valor de índice par:

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

A instrução acima retorna a seguinte tabela:

valor	Ordinal
Texas	2
Washington	4
Colorado	6

G. Ordenar linhas por valores ordinais

A instrução a seguir retorna os valores de substring dividida da cadeia de caracteres de entrada e seus valores ordinais, ordenados pela ordinal coluna:

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