SQLShack
Gli sviluppatori di database hanno spesso bisogno di convertire un valore separato da virgole o altri elementi delimitati in un formato tabellare. I delimitatori includono pipe “|”, hash “#”, dollaro “$” e altri caratteri. Il livello di compatibilità 130 di SQL Server, e le versioni successive, supportano una funzione string_split() per convertire valori separati da delimitatori in righe (formato tabella). Per i livelli di compatibilità inferiori al 130, gli sviluppatori hanno precedentemente fatto questo con una funzione definita dall’utente, che incorpora un ciclo While o un Cursore per estrarre i dati.
Perché è necessario un input separato da virgole per una procedura o una query T-SQL?
Eseguire un programma in una iterazione usando un ciclo è un metodo comune nell’applicazione back-end così come nel database. Quando il numero di iterazioni è più piccolo del previsto, il tempo di esecuzione può essere inferiore, tuttavia il numero di iterazioni è più grande, il che porta a tempi di elaborazione più lunghi. Per esempio, se l’applicazione sta recuperando dati da un database con l’esecuzione di un pezzo di programma in un ciclo analizzando diversi parametri di input, l’applicazione avrà bisogno di avvolgere la risposta del database di ogni iterazione. Per gestire questo tipo di situazione, possiamo eseguire lo stesso programma con una singola esecuzione con l’analisi dei valori separati da virgola o delimitatore come parametro di input nel database e renderlo in formato tabulare nel programma T-SQL per procedere con ulteriore logica.
Oggi, molte piattaforme sono passate all’architettura a microservizi. Non solo l’applicazione ma anche la progettazione del database potrebbe essere strutturata in un’architettura basata sui microservizi e la maggior parte della comunicazione interna tra i database è fatta solo con riferimenti interi. Questo è il motivo per ottenere qualche modo complicato per ottenere una migliore esecuzione dal lato del database.
Più input allo stesso parametro possono essere realizzati con valori separati da virgole nel parametro di input della stored procedure o input alla funzione tabellare, e utilizzati con la tabella in una dichiarazione T-SQL. Ci possono essere più situazioni per questa strategia quando si usa il T-SQL. Durante la programmazione con SQL Server, uno sviluppatore deve spezzare una stringa in elementi usando un separatore. Usando la funzione split contro la stringa definisce il separatore come parametro di input. L’intera stringa sarà divisa e restituita come tabella. In alcune circostanze, il parametro di input può essere una combinazione di due o più valori separati da caratteri per ogni set di input. Per esempio:
N’203616, 198667, 193718, 188769,…’ N’1021|203616$1021|198667$1022|193718$1022|188769$…’ N’1021|203616,198667$1022|193718,188769$…’
Ci sono dei vantaggi nell’esecuzione a thread singolo con valori separati da virgola rispetto all’esecuzione multi-thread in SQL Server:
- Esecuzione singola per una parte del programma, con conseguente numero di input
- Singola operazione di lettura o scrittura sulle tabelle del database invece di diverse iterazioni utilizzando un ciclo
- Utilizzo una sola volta delle risorse del server
- Niente più ostacoli per l’applicazione back-end per impostare una risposta da più set di risultati
- Un commit one-shot significa che il database/SQL Server non blocca la transazione e rallenta le cose. Quindi, nessuna possibilità di blocco
- Facile sorvegliare la consistenza della transazione
Funzione di divisione usando il ciclo
Nella maggior parte dei casi, uno sviluppatore scrive la funzione con valore di tabella con un ciclo per estrarre i dati da valori separati da virgola a un elenco o a una tabella. Un ciclo estrae le tuple una per una con la ricorsione. I problemi di performance possono sorgere quando si lavora con un grande parametro di input, poiché ci vorrà più tempo per estrarlo. Qui, abbiamo una semplice funzione con il nome di split_string, che sta usando un ciclo all’interno della funzione con valore di tabella. Questa funzione permette due argomenti o parametri di input (1 – stringa di input e 2 – delimitatore):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE FUNCTION split_string
(
@in_string VARCHAR(MAX),
@delimetro VARCHAR(1)
)
RETURNS @list TABLE(tuple VARCHAR(100))
AS
BEGIN
WHILE LEN(@in_string) > 0
BEGIN
INSERT INTO @list(tuple)
SELECT left(@in_string, charindex(@delimiter, @in_string+’,’) -1) come tupla
SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), ”)
fine
RETURN
END
|
Una tabella di risultati restituita da questa funzione contiene ogni sottostringa della stringa del parametro di input che è separata dal delimitatore. Le sottostringhe nella tabella sono nell’ordine in cui si trovano nel parametro di input. Se il separatore o il delimitatore non è coordinato con nessun pezzo della stringa di input, allora la tabella di output avrà un solo componente. La funzione tabular restituirà l’insieme dei risultati in un formato riga-colonna dalla stringa separata da virgole.
|
1
|
SELECT * FROM split_string(‘1001,1002,1003,1004’, ‘,’)
|
Le versioni recenti di SQL Server forniscono una funzione integrata string_split() per eseguire lo stesso compito con i parametri di input della stringa e delimitatore. Ma c’è un altro modo efficiente per eseguire questo compito usando XML.
Funzione Split usando XML
La funzione Split usando la logica XML è un metodo utile per separare valori da una stringa delimitata. Usando la logica XML con l’approccio XQUERY basato su T-SQL, il T-SQL non è caratterizzato come una ricorsione che utilizza un ciclo o un cursore. Essenzialmente, la stringa dei parametri di input viene convertita in XML sostituendo il nodo XML nell’articolazione T-SQL. L’XML risultante viene utilizzato in XQUERY per estrarre il valore del nodo nel formato della tabella. Risulta anche in una migliore performance.
Per esempio, abbiamo un valore separato da virgole con la variabile @user_ids nella seguente dichiarazione T-SQL. In una variabile, la virgola è sostituita dai tag XML per renderla un documento di tipo dati XML e poi estratta usando XQUERY con l’XPATH come ‘/root/U’:
|
1
2
3
4
5
6
7
8
9
10
|
DECLARE @user_ids NVARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871, 173922, 168973, 164024, 159075, 154126, 149177, 144228, 139279, 134330, 129381, 124432, 119483, 114534, 109585, 104636, 99687, 94738, 89789, 84840, 79891, 74942, 69993, 65044, 60095, 55146′
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, ‘</U><U></U></root>’ AS XML)
SELECT f.x.value(‘.’, ‘BIGINT’) AS user_id
INTO #users
FROM @sql_xml.nodes(‘/root/U’) f(x)
SELECT *
FROM #users
|
Nella funzione sopra, abbiamo usato il tipo di dati VARCHAR (100) per il valore estratto piuttosto che BIGINT. Ci possono essere cambiamenti anche nella stringa del parametro di input. Per esempio, se la stringa di input ha un ‘,’ in più all’inizio o alla fine della stringa, restituisce una riga in più con 0 per il tipo di dati BIGINT e ” per VARCHAR.
Per esempio,
|
1
2
3
4
5
|
DECLARE @user_ids VARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871,’
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, ‘</U><U></U></root>’ AS XML)
SELECT f.x.value(‘.’, ‘BIGINT’) AS user_id
FROM @sql_xml.nodes(‘/root/U’) f(x)
|
Per gestire questo tipo di dati ” o 0 nel set di risultati, possiamo aggiungere la condizione qui sotto nell’istruzione T-SQL:
|
1
2
3
4
5
6
|
DECLARE @user_ids VARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871,’
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, ‘</U><U></U></root>’ AS XML)
SELECT f.x.value(‘.’, ‘BIGINT’) AS user_id
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘BIGINT’) <> 0
|
Qui, f.x.value(‘.’, ‘BIGINT’) <> 0 esclude lo 0 e f.x.value(‘.’, ‘VARCHAR(100)’) <> ” esclude il ” nel set di risultati della query. Questa condizione T-SQL può essere aggiunta nelle funzioni con valore di tabella con il numero di parametro di input per gestire questo delimitatore extra.
Funzione:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE FUNCTION split_string_XML
(
@in_string VARCHAR(MAX),
@delimetro VARCHAR(1)
)
RETURNS @list TABLE(tuple VARCHAR(100))
AS
BEGIN
DECLARE @sql_xml XML = Cast(‘< radice><U>’+ Replace(@in_string, @delimetro, ‘</U><U></U></root>’ AS XML)
INSERT INTO @list(tuple)
SELECT f.x.value(‘.’, ‘VARCHAR(100)’) AS tuple
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘BIGINT’) <> 0
RETURN
END
|
Esecuzione:
|
1
2
|
SELECT *
FROM split_string_XML(‘203616,198667,193718,188769,183820,178871,173922,’, ‘,’)
|
Combinazione di caratteri in stringhe separate da delimitatori
Ma cosa succede quando il parametro di input è una combinazione di due valori con più separatori? In questo caso, il parametro di input deve essere estratto due volte con l’aiuto di una subquery, come mostrato di seguito:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE @in_stringa VARCHAR(MAX) = ‘1021|203616$1021|198667$1022|193718$1022|188769 XML = Cast(‘<root><U>’+ Replace(@in_string, ‘ ‘</U><U></U></root>’ AS XML)
SELECT X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS role_id,
X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS user_id
FROM
(
SELECT Cast(‘<root><U>’+ Replace(f.x.value(‘.’, ‘VARCHAR(MAX)’), ‘|’, ‘</U><U></U></root>’ AS XML) AS xml_
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘VARCHAR(MAX)’) <> ”
)T
OUTER APPLY T.xml_.nodes(‘root’) as X(Y)
|
Questo esempio è simile a quello precedente ma abbiamo utilizzato pipe “|” come delimitatore con un secondo delimitatore, dollaro “$”. Qui, il parametro di input è la combinazione di due colonne, role_id e user_id. Come vediamo sotto, user_id e role_id sono in una colonna separata nel set di risultati della tabella:
Possiamo usare qualsiasi carattere nella funzione di cui sopra, come una singola stringa separata da caratteri o una combinazione di stringhe separate da caratteri. Uno sviluppatore deve semplicemente sostituire il carattere nelle dichiarazioni T-SQL di cui sopra. Seguite i passi descritti qui per convertire qualsiasi stringa delimitata in una lista. Basta ricordare che la funzione XML di SQL Server accetta un input di tipo stringa e la stringa di input sarà delimitata con il separatore specificato utilizzando la funzione tabulare.
- Autore
- Post recenti
Vedi tutti i post di Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – 17 luglio, 2020
- Come automatizzare il partizionamento delle tabelle in SQL Server – 7 luglio 2020
- Configurazione di SQL Server Always On Availability Groups su AWS EC2 – 6 luglio 2020
This example is similar to the above example but we have utilized pipe “|” as a delimiter with a second delimiter, dollar “$”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020
, ‘</U><U></U></root>’ AS XML)
This example is similar to the above example but we have utilized pipe “|” as a delimiter with a second delimiter, dollar “$”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020
This example is similar to the above example but we have utilized pipe “|” as a delimiter with a second delimiter, dollar “$”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020