SQLShack
Datenbankentwickler müssen oft einen kommagetrennten Wert oder andere begrenzte Elemente in ein Tabellenformat konvertieren. Zu den Begrenzungszeichen gehören Pipe „|“, Raute „#“, Dollar „$“ und andere Zeichen. SQL Server-Kompatibilitätsebene 130 und nachfolgende Versionen unterstützen eine string_split()-Funktion zur Konvertierung von durch Begrenzungszeichen getrennten Werten in Zeilen (Tabellenformat). Bei Kompatibilitätsstufen unter 130 haben Entwickler dies bisher mit einer benutzerdefinierten Funktion getan, die eine While-Schleife oder einen Cursor zum Extrahieren der Daten enthält.
Warum ist eine kommagetrennte Eingabe für eine Prozedur oder T-SQL-Abfrage erforderlich?
Das Ausführen eines Programms in einer Iteration unter Verwendung einer Schleife ist eine gängige Methode sowohl in der Back-End-Anwendung als auch in der Datenbank. Wenn die Anzahl der Iterationen kleiner als erwartet ist, kann die Ausführungszeit geringer sein, allerdings ist die Anzahl der Iterationen größer, was zu einer längeren Verarbeitungszeit führt. Wenn die Anwendung z. B. Daten aus einer Datenbank abruft und dabei ein Programmteil in einer Schleife ausführt, indem verschiedene Eingabeparameter geparst werden, muss die Anwendung die Datenbankantwort jeder Iteration verpacken. Um mit dieser Art von Situation umzugehen, können wir dasselbe Programm mit einer einzigen Ausführung ausführen, wobei die durch Komma oder Trennzeichen getrennten Werte als Eingabeparameter in der Datenbank geparst und im T-SQL-Programm in ein tabellarisches Format gebracht werden, um mit der weiteren Logik fortzufahren.
Heutzutage sind viele Plattformen auf eine Microservice-Architektur umgestellt worden. Nicht nur die Anwendung, sondern auch das Datenbankdesign könnte in einer Microservice-basierten Architektur strukturiert sein, und die meiste interne Kommunikation zwischen den Datenbanken wird nur mit Integer-Referenzen durchgeführt. Das ist der Grund, warum man einen trickreichen Weg finden muss, um eine bessere Ausführung von der Datenbankseite aus zu erreichen.
Mehrere Eingaben für denselben Parameter können mit kommagetrennten Werten im Eingabeparameter der gespeicherten Prozedur oder der Eingabe für die tabellarische Funktion erreicht werden, und mit der Tabelle in einer T-SQL-Anweisung verwendet werden. Bei der Verwendung von T-SQL kann es mehrere Situationen für diese Strategie geben. Bei der Programmierung mit SQL Server muss ein Entwickler eine Zeichenkette mithilfe eines Trennzeichens in Elemente aufteilen. Durch die Verwendung der Split-Funktion gegen die Zeichenkette wird das Trennzeichen als Eingabeparameter definiert. Die gesamte Zeichenkette wird aufgeteilt und als Tabelle zurückgegeben. Unter bestimmten Umständen kann der Eingabeparameter eine Kombination aus zwei oder mehr zeichengetrennten Werten für jeden Satz von Eingaben sein. Zum Beispiel:
N’203616, 198667, 193718, 188769,…‘ N’1021|203616$1021|198667$1022|193718$1022|188769$…‘ N’1021|203616,198667$1022|193718,188769$…‘
Die Einzel-Thread-Ausführung mit kommagetrennten Werten hat gegenüber der Multi-Thread-Ausführung in SQL Server einige Vorteile:
- Einmalige Ausführung für einen Teil des Programms, wodurch sich die Anzahl der Eingaben verringert
- Ein einziger Lese- oder Schreibvorgang über die Datenbanktabellen anstelle mehrerer Iterationen mit einer Schleife
- Einmalige Verwendung des Server-Assets
- Keine weiteren Hürden für die Back-End-Anwendung, um eine Antwort aus mehreren Ergebnismengen zu erstellen
- Einmaliges Commit bedeutet, dass die Datenbank/der SQL Server die Transaktion nicht blockiert und damit alles verlangsamt. Daher keine Blockierungsmöglichkeiten
- Einfache Überwachung der Transaktionskonsistenz
Split-Funktion mit Schleife
In den meisten Fällen schreibt ein Entwickler die tabellenwertige Funktion mit einer Schleife, um die Daten von kommagetrennten Werten in ein Listen- oder Tabellenformat zu extrahieren. Eine Schleife extrahiert das Tupel eins nach dem anderen mit der Rekursion. Bei der Arbeit mit großen Eingabeparametern können Leistungsprobleme auftreten, da das Extrahieren mehr Zeit in Anspruch nimmt. Hier haben wir eine einfache Funktion mit dem Namen split_string, die eine Schleife innerhalb der tabellenwertigen Funktion verwendet. Diese Funktion erlaubt zwei Argumente oder Eingabeparameter (1 – Eingabezeichenfolge und 2 – Trennzeichen):
|
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),
@delimeter 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) as tuple
SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), “)
end
RETURN
END
|
Eine von dieser Funktion zurückgegebene Tabelle der Ergebnismenge enthält jede Teilzeichenkette des Eingabeparameters string, die durch das Trennzeichen getrennt ist. Die Teilzeichenfolgen in der Tabelle stehen in der Reihenfolge, in der sie im Eingabeparameter vorkommen. Wenn das Trennzeichen oder der Begrenzer mit keinem Teil der Eingangszeichenkette übereinstimmt, dann hat die Ausgabetabelle nur eine Komponente. Die tabellarische Funktion gibt die Ergebnismenge in einem Zeilen-Spalten-Format aus der kommagetrennten Zeichenkette zurück.
|
1
|
SELECT * FROM split_string(‚1001,1002,1003,1004‘, ‚,‘)
|
Jüngere Versionen von SQL Server stellen eine eingebaute Funktion string_split() zur Verfügung, die die gleiche Aufgabe mit den Eingabeparametern der Eingabezeichenkette und des Trennzeichens erledigt. Es gibt jedoch noch eine andere effiziente Möglichkeit, diese Aufgabe mit Hilfe von XML zu erledigen.
Split-Funktion mit XML
Die Split-Funktion mit XML-Logik ist eine nützliche Methode zum Trennen von Werten aus einem begrenzten String. Die Verwendung von XML-Logik mit dem auf T-SQL basierenden XQUERY-Ansatz führt dazu, dass das T-SQL nicht als Rekursion mit Schleife oder Cursor charakterisiert wird. Im Wesentlichen wird der Input-Parameterstring in XML konvertiert, indem der XML-Node in der T-SQL-Gliederung ersetzt wird. Das resultierende XML wird in XQUERY verwendet, um den Knotenwert in das Tabellenformat zu extrahieren. Dadurch wird auch eine bessere Leistung erzielt.
In der folgenden T-SQL-Anweisung haben wir z.B. einen kommaseparierten Wert mit der Variablen @user_ids. In der Variable wird das Komma durch die XML-Tags ersetzt, um sie zu einem Dokument vom Typ XML zu machen, und dann mit XQUERY mit dem XPATH als ‚/root/U‘ extrahiert:
|
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
|
In der obigen Funktion, haben wir den Datentyp VARCHAR (100) für den extrahierten Wert anstelle von BIGINT verwendet. Auch in der Eingabeparameter-Zeichenkette kann es Änderungen geben. Wenn die Eingabezeichenkette beispielsweise ein zusätzliches ‚,‘ am Anfang oder Ende der Zeichenkette enthält, wird eine zusätzliche Zeile mit 0 für den BIGINT-Datentyp und “ für VARCHAR zurückgegeben.
Beispiel,
|
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)
|
Um diesen Typ von “ oder 0 Daten in der Ergebnismenge zu behandeln, können wir die folgende Bedingung in der T-SQL-Anweisung hinzufügen:
|
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
|
Hier wird f.x.value(‚.‘, ‚BIGINT‘) <> 0 die 0 und f.x.value(‚.‘, ‚VARCHAR(100)‘) <> “ die “ in der Abfrageergebnismenge ausschließen. Diese T-SQL-Bedingung kann in die tabellenwertigen Funktionen mit der Anzahl der Eingabeparameter eingefügt werden, um diesen zusätzlichen Begrenzer zu behandeln.
Funktion:
|
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),
@delimeter VARCHAR(1)
)
RETURNS @list TABLE(tuple VARCHAR(100))
AS
BEGIN
DECLARE @sql_xml XML = Cast(‚<root><U>’+ Replace(@in_string, @delimeter, ‚</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
|
Ausführung:
|
1
2
|
SELECT *
FROM split_string_XML(‚203616,198667,193718,188769,183820,178871,173922,‘, ‚,‘)
|
Kombination von Zeichen in trennzeichengetrennter Zeichenkette
Aber was ist, wenn der Eingabeparameter eine Kombination aus zwei Werten mit mehreren Trennzeichen ist? In diesem Fall muss der Eingabeparameter zweimal mit Hilfe einer Subquery extrahiert werden, wie unten gezeigt:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE @in_string 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)
|
Dieses Beispiel ähnelt dem obigen Beispiel, aber wir haben Pipe „|“ als Begrenzer mit einem zweiten Begrenzer, Dollar „$“, verwendet. Hier ist der Eingabeparameter die Kombination aus zwei Spalten, role_id und user_id. Wie wir unten sehen, befinden sich user_id und role_id in einer separaten Spalte in der Ergebnismenge der Tabelle:
Wir können in der obigen Funktion jedes beliebige Zeichen verwenden, z. B. eine einzelne zeichengetrennte Zeichenkette oder eine Kombination von zeichengetrennten Zeichenketten. Ein Entwickler muss lediglich das Zeichen in den obigen T-SQL-Anweisungen ersetzen. Führen Sie die hier beschriebenen Schritte aus, um einen beliebigen begrenzten String in eine Liste zu konvertieren. Denken Sie einfach daran, dass die XML-Funktion von SQL Server eine String-Eingabe akzeptiert und der Eingabestring mit dem angegebenen Trennzeichen durch die Tabellenfunktion abgegrenzt wird.
- Autor
- Recent Posts
Alle Beiträge von Jignesh Raiyani anzeigen
- Page Life Expectancy (PLE) in SQL Server – Juli 17, 2020
- Wie man die Tabellenpartitionierung in SQL Server automatisiert – 7. Juli 2020
- Konfigurieren von SQL Server Always On Availability Groups auf AWS EC2 – 6. Juli 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