STRING_SPLIT (Transact-SQL)

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure Istanza gestita di SQL di Azure Endpoint di analisi SQL di Azure Synapse Analyticsin Microsoft FabricWarehouse in Microsoft Fabric

STRING_SPLIT è una funzione con valori di tabella che suddivide una stringa in righe di sottostringhe, in base a un carattere separatore specificato.

Livello di compatibilità 130

STRING_SPLIT richiede che il livello di compatibilità sia almeno 130. Quando il livello è minore di 130, il motore di database non riesce a trovare la STRING_SPLIT funzione.

Per modificare il livello di compatibilità di un database, fare riferimento a Visualizzare o modificare il livello di compatibilità di un database.

Nota

La configurazione della compatibilità non è necessaria per STRING_SPLIT in Azure Synapse Analytics.

Convenzioni di sintassi Transact-SQL

Sintassi

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Argomenti

string

Espressione di qualsiasi tipo di carattere, ad esempio nvarchar, varchar, nchar o char.

separator

Espressione a carattere singolo di qualsiasi tipo di carattere (ad esempio, nvarchar(1), varchar(1), nchar(1)o char(1)) utilizzata come separatore per le sottostringhe concatenate.

enable_ordinal

Si applica a: database SQL di Azure, Istanza gestita di SQL di Azure, Azure Synapse Analytics (solo pool SQL serverless) e SQL Server 2022 (16.x) e versioni successive

Espressioneint o bit che funge da flag per abilitare o disabilitare la colonna di output ordinal. Un valore di 1 abilita la ordinal colonna. Se enable_ordinal viene omesso, NULLo ha un valore , 0la ordinal colonna è disabilitata.

Tipi restituiti

Se la ordinal colonna di output non è abilitata, STRING_SPLIT restituisce una tabella a colonna singola le cui righe sono le sottostringhe. La colonna è denominata value. Restituisce nvarchar se uno qualsiasi degli argomenti di input è nvarchar o nchar. In caso contrario, restituisce varchar. La lunghezza del tipo restituito è uguale a quella dell'argomento string.

Se l'argomento enable_ordinal viene passato un valore pari 1a , viene restituita una seconda colonna denominata ordinal costituita dai valori di indice in base 1 della posizione di ogni sottostringa nella stringa di input. Il tipo restituito è bigint.

Osservazioni:

STRING_SPLIT inserisce una stringa con sottostringhe delimitate e inserisce un carattere da usare come delimitatore o separatore. Facoltativamente, la funzione supporta un terzo argomento con un valore 0 o 1 che disabilita o abilita, rispettivamente, la ordinal colonna di output.

STRING_SPLIT restituisce una tabella a colonna singola o a colonna doppia, a seconda dell'argomento enable_ordinal .

  • Se enable_ordinal è NULL, omesso o ha un valore , 0STRING_SPLIT restituisce una tabella a colonna singola le cui righe contengono le sottostringhe. Il nome della colonna di output è value.

  • Se enable_ordinal ha un valore , 1la funzione restituisce una tabella a due colonne, inclusa la ordinal colonna costituita dai valori di indice in base 1 delle sottostringhe nella stringa di input originale.

L'argomento enable_ordinal deve essere un valore costante, non una colonna o una variabile. Deve anche essere un tipo di dati bit o int con un valore di 0 o 1. In caso contrario, la funzione genera un errore.

Le righe di output potrebbero essere in qualsiasi ordine. L'ordine non corrisponde all'ordine delle sottostringhe nella stringa di input. È possibile eseguire l'override dell'ordinamento finale usando una ORDER BY clausola nell'istruzione SELECT , ORDER BY value ad esempio o ORDER BY ordinal.

0x0000 (char(0)) è un carattere non definito nelle regole di confronto di Windows e non può essere incluso in STRING_SPLIT.

Le sottostringhe vuote di lunghezza zero sono presenti quando la stringa di input contiene due o più occorrenze consecutive del carattere delimitatore. Le sottostringhe vuote vengono trattate allo stesso modo delle sottostringhe semplici. È possibile filtrare tutte le righe che contengono la sottostringa vuota usando la WHERE clausola , ad esempio WHERE value <> ''. Se la stringa di input è NULL, la STRING_SPLIT funzione con valori di tabella restituisce una tabella vuota.

Ad esempio, l'istruzione seguente SELECT usa lo spazio come separatore:

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

In un'esecuzione pratica, la SELECT tabella dei risultati precedente ha restituito la tabella dei risultati seguente:

value
Lorem
ipsum
dolor
sit
amet.

Nell'esempio seguente viene abilitata la ordinal colonna passando 1 per il terzo argomento facoltativo:

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

Questa istruzione restituisce quindi la tabella dei risultati seguente:

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

Esempi

R. Dividere una stringa di valori separati da virgola

Analizzare un elenco di valori separati da virgole e restituire tutti i token non vuoti:

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

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

STRING_SPLIT restituisce una stringa vuota se non è presente alcun separatore. La condizione RTRIM(value) <> '' rimuove i token vuoti.

B. Dividere una stringa di valori delimitati da virgola in una colonna

La tabella Product ha una colonna con un elenco di tag delimitati da virgole illustrato nell'esempio seguente:

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

La query seguente trasforma gli elenchi di tag e li unisce alla riga originale:

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

Questo è il set di risultati.

ProductId Name valore
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

Nota

L'ordine dell'output può variare perché l'ordine non corrisponde all'ordine delle sottostringhe nella stringa di input.

C. Aggregazione per valori

Gli utenti devono creare un report che visualizzi il numero di prodotti per ogni tag, ordinati in base al numero di prodotti, e che sia possibile filtrare in base ai tag con più di due prodotti.

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. Ricerca in base al valore del tag

Gli sviluppatori devono creare query per trovare articoli in base a parole chiave. Possono usare le query seguenti:

Per trovare i prodotti con un singolo tag (clothing):

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

Per trovare i prodotti con due tag specificati (clothing e road):

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

E. Trovare le righe in base all'elenco di valori

Gli sviluppatori devono creare una query che consenta di trovare gli articoli in base a un elenco di ID. Possono usare la query seguente:

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

L'utilizzo precedente STRING_SPLIT è una sostituzione di un antipattern comune. Un antipattern di questo tipo può comportare la creazione di una stringa SQL dinamica nel livello dell'applicazione o in Transact-SQL. In alternativa, è possibile ottenere un antipattern usando l'operatore LIKE . Vedere l'istruzione di esempio SELECT seguente:

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

F. Trovare le righe in base ai valori ordinali

L'istruzione seguente trova tutte le righe con un valore di indice pari:

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

L'istruzione precedente restituisce la tabella seguente:

value ordinal
Texas 2
Washington 4
Colorado 6

G. Ordinare le righe in base ai valori ordinali

L'istruzione seguente restituisce i valori delle sottostringhe divise della stringa di input e i relativi valori ordinali, ordinati in base alla colonna ordinal:

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

L'istruzione precedente restituisce la tabella seguente:

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

Contenuto correlato