STRING_SPLIT (Transact-SQL)

APLICA-SE A: SQL Server 2016 e posterior Banco de Dados SQL do Azure Azure Synapse Analytics Parallel Data Warehouse

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 for inferior a 130, o SQL Server não conseguirá 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.

Ícone de link do tópico Convenções da sintaxe Transact-SQL

Sintaxe

STRING_SPLIT ( string , separator )  

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

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.

Tipos de retorno

Retorna uma tabela de coluna única cujas linhas são as subcadeias de caracteres. O nome da coluna é value. Retorna nvarchar se um dos argumentos de entrada são nvarchar ou nchar. Caso contrário, retorna varchar. O tamanho do tipo de retorno é o mesmo que o tamanho do argumento da cadeia de caracteres.

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. STRING_SPLIT gera uma tabela de coluna única cujas linhas contêm as subcadeias de caracteres. O nome da coluna de saída é value.

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 (ORDER BY value).

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 (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 SELECT anterior retornava a seguinte tabela de resultado:

value
Lorem
ipsum
dolor
sit
amet.
 

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) <> '' removerá 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 value
1 Full-Finger Gloves clothing
1 Full-Finger Gloves rodoviário
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)) + ',%';  

Consulte Também