SQLShack
Programiści baz danych często potrzebują przekonwertować wartości rozdzielone przecinkami lub inne elementy delimitowane do formatu tabelarycznego. Delimitery to m.in. pipe „|”, hash „#”, dolar „$” i inne znaki. Poziom zgodności SQL Server 130, i kolejne wersje, wspierają funkcję string_split() do konwersji wartości rozdzielonych delimiterami na wiersze (format tabelaryczny). Dla poziomów kompatybilności poniżej 130, programiści robili to wcześniej za pomocą funkcji zdefiniowanej przez użytkownika, która zawiera pętlę While lub Cursor do wyodrębnienia danych.
Dlaczego dane wejściowe rozdzielone przecinkami są wymagane dla procedury lub zapytania T-SQL?
Wykonywanie programu w iteracji za pomocą pętli jest powszechną metodą zarówno w aplikacji back-end jak i w bazie danych. Gdy liczba iteracji jest mniejsza niż oczekiwana, czas wykonania może być mniejszy, jednak liczba iteracji jest większa, co prowadzi do wydłużenia czasu przetwarzania. Na przykład, jeśli aplikacja pobiera dane z bazy danych wykonując fragment programu w pętli poprzez parsowanie różnych parametrów wejściowych, aplikacja będzie musiała zawijać odpowiedź bazy danych w każdej iteracji. Aby poradzić sobie z tego typu sytuacją, możemy wykonać ten sam program w pojedynczym wykonaniu z parsowaniem wartości rozdzielonych przecinkami lub delimiterami jako parametru wejściowego w bazie danych i przekształcić je do formatu tabelarycznego w programie T-SQL, aby kontynuować dalszą logikę.
W dzisiejszych czasach wiele platform zostało przestawionych na architekturę mikrousługową. Nie tylko aplikacja, ale także projekt bazy danych może być zorganizowany w architekturze opartej na mikrousługach, a większość wewnętrznej komunikacji między bazami danych odbywa się tylko za pomocą odniesień do liczb całkowitych. To jest powód, aby uzyskać pewien podstępny sposób, aby osiągnąć lepsze wykonanie od strony bazy danych.
Wiele wejść do tego samego parametru można osiągnąć za pomocą wartości oddzielonych przecinkami w parametrze wejściowym procedury przechowywanej lub wejścia do funkcji tabelarycznej, a następnie użyć ich z tabelą w instrukcji T-SQL. Może być więcej sytuacji dla tej strategii podczas używania T-SQL. Podczas programowania w SQL Server, programista musi rozbić ciąg znaków na elementy przy użyciu separatora. Użycie funkcji split w stosunku do łańcucha znaków definiuje separator jako parametr wejściowy. Cały łańcuch zostanie podzielony i zwrócony jako tablica. W pewnych okolicznościach, parametr wejściowy może być kombinacją dwóch lub więcej wartości oddzielonych znakami dla każdego zestawu danych wejściowych. Na przykład:
N’203616, 198667, 193718, 188769,…' N’1021|203616$1021|198667$1022|193718$1022|188769$…' N’1021|203616,198667$1022|193718,188769$…'
W SQL Server istnieją zalety wykonywania jednowątkowego z wartościami rozdzielanymi przecinkami w stosunku do wykonywania wielowątkowego:
- Pojedyncze wykonanie dla części programu, co skutkuje zmniejszeniem liczby wejść
- Pojedyncza operacja odczytu lub zapisu nad tabelami bazy danych zamiast kilku iteracji przy użyciu pętli
- Jednorazowe użycie zasobów serwera
- Brak dodatkowych utrudnień na aplikacji back-endowej w celu skonfigurowania odpowiedzi z wielu zestawów wyników
- One-shot commit oznacza, że baza danych/SQL Server nie zablokuje transakcji i nie spowolni działania. Dzięki temu nie ma możliwości blokowania transakcji
- Łatwe nadzorowanie spójności transakcji
Podziel funkcję za pomocą pętli
W większości przypadków programista pisze funkcję tabelaryczną z pętlą, aby wyodrębnić dane z wartości rozdzielanych przecinkami do formatu listy lub tabeli. Pętla wyodrębnia krotki jedna po drugiej za pomocą rekurencji. Problemy z wydajnością mogą pojawić się podczas pracy z dużym parametrem wejściowym, ponieważ wyodrębnienie go zajmie więcej czasu. Tutaj mamy prostą funkcję o nazwie split_string, która używa pętli wewnątrz funkcji tablicowo-wartościowej. Funkcja ta pozwala na dwa argumenty lub parametry wejściowe (1 – łańcuch wejściowy i 2 – delimiter):
|
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
|
Tablica result-set zwracana przez tę funkcję zawiera każdy podłańcuch parametru wejściowego string, który jest oddzielony delimiterem. Podłańcuchy w tabeli są w kolejności, w jakiej występują w parametrze wejściowym. Jeżeli separator lub ogranicznik nie jest skoordynowany z żadnym fragmentem łańcucha wejściowego, to tablica wyjściowa będzie miała tylko jeden element. Funkcja tabelaryczna zwróci zestaw wyników w formacie wiersz-kolumna z łańcucha rozdzielonego przecinkami.
|
1
|
SELECT * FROM split_string(’1001,1002,1003,1004′, ',')
|
Najnowsze wersje SQL Server dostarczają wbudowaną funkcję string_split() do wykonania tego samego zadania z parametrami wejściowymi ciągu znaków i delimitera. Istnieje jednak inny skuteczny sposób wykonania tego zadania przy użyciu XML.
Funkcja Split przy użyciu XML
Funkcja Split przy użyciu logiki XML jest użyteczną metodą oddzielania wartości od delimitowanego łańcucha. Użycie logiki XML z podejściem XQUERY opartym na T-SQL powoduje, że T-SQL nie jest charakteryzowany jako rekurencja wykorzystująca pętlę lub kursor. Zasadniczo, wejściowy ciąg parametrów jest konwertowany na XML poprzez zastąpienie węzła XML w wyrażeniu T-SQL. Wynikowy XML jest używany w XQUERY do wyodrębnienia wartości węzła do formatu tabeli. Powoduje to również lepszą wydajność.
Na przykład, mamy wartość rozdzieloną przecinkami ze zmienną @user_ids w poniższej instrukcji T-SQL. W zmiennej, przecinek jest zastępowany przez znaczniki XML, aby uczynić ją dokumentem typu danych XML, a następnie wyodrębniony przy użyciu XQUERY z XPATH jako '/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
|
W powyższej funkcji, użyliśmy typu danych VARCHAR (100) dla wyodrębnianej wartości zamiast BIGINT. W łańcuchu parametrów wejściowych również mogą nastąpić zmiany. Na przykład, jeśli łańcuch wejściowy ma jeden dodatkowy znak ',' na początku lub końcu łańcucha, zwraca jeden dodatkowy wiersz z 0 dla typu danych BIGINT i ” dla VARCHAR.
Na przykład,
|
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)
|
Aby obsłużyć tego typu ” lub 0 danych w zbiorze wyników, możemy dodać poniższy warunek w instrukcji 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
|
W tym miejscu f.x.value('.', 'BIGINT') <> 0 wyklucza 0, a f.x.value('.', 'VARCHAR(100)') <> ” wyklucza ” w zbiorze wyników zapytania. Ten warunek T-SQL można dodać do funkcji wartościowanych w tabeli z liczbą parametru wejściowego, aby obsłużyć ten dodatkowy delimiter.
Funkcja:
|
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
|
Wykonanie:
|
1
2
|
SELECT *
FROM split_string_XML(’203616,198667,193718,188769,183820,178871,173922,', ',')
|
Kombinacja znaków w delimiter-separated string
Ale co w przypadku, gdy parametr wejściowy jest kombinacją dwóch wartości z wieloma separatorami? W takim przypadku parametr wejściowy musi zostać wyodrębniony dwukrotnie za pomocą podzapytania, jak pokazano poniżej:
|
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)
|
Ten przykład jest podobny do powyższego przykładu, ale wykorzystaliśmy pipe „|” jako delimiter z drugim delimiterem, dolarem „$”. Tutaj, parametrem wejściowym jest kombinacja dwóch kolumn, role_id, oraz user_id. Jak widzimy poniżej, user_id oraz role_id znajdują się w osobnej kolumnie w tabeli wyników:
W powyższej funkcji możemy użyć dowolnego znaku, np. pojedynczego łańcucha oddzielonego znakami lub kombinacji łańcuchów oddzielonych znakami. Programista musi po prostu zastąpić ten znak w powyższych instrukcjach T-SQL. Wykonaj opisane tutaj kroki, aby przekonwertować dowolny delimitowany łańcuch na listę. Wystarczy pamiętać, że funkcja XML programu SQL Server akceptuje łańcuch wejściowy, a łańcuch wejściowy zostanie ograniczony określonym separatorem za pomocą funkcji tabelarycznej.
- Autor
- Recentent 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
, '</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