Pre nekog vremena smo u dva članka (1, 2) opisali proces konkatenacije, odnosno spajanja vrednosti iz kolone u jedan string. Ovog puta prikazaćemo obrnut proces – kako iz jednog csv stringa dobiti sve pojedinačne vrednosti. Kako je ovaj proces malo složeniji i za pojedinačni string treba da vrati tabelu, obično se u te svrhe koriste table-valued funkcije, tzv. splitting funkcije.

Odmah da napomenemo da je najispravnija varijanta za splitting funkciju korišćenje CLR. Međutim, to ćemo ostaviti za neki naredni članak, a u ovom ćemo opisati nekoliko T-SQL rešenja. Njih postoji dosta, a mi smo izabrali pet koje smo prilagodili i adaptirali tako da sve funkcije imaju iste ulazne parametre i vraćaju iste rezultate:

• Prvi ulazni parametar je string koji se splituje, u NVARCHAR(MAX) formatu
• Drugi ulazni parametar je delimiter, koji i sam može imati do 255 znakova
• Ukoliko delimiter nije prosleđen koristi se zarez (,)
• Rezultat funkcije je tabela sa dve kolone: RedniBroj i Element
• Povratni elementi su tipa NVARCHAR(4000)
• Blanko znakovi na početku svakog elementa se uklanjaju

While petlja

Prvo rešenje koje ćemo opisati je ujedno i ono koje se najčešće koristi. Odseca se prvi element, a zatim u while petlji ponavlja proces nad ostatkom stringa.

Ovo je jednostavno rešenje, koje služi svrsi u većini slučajeva, ali na dužim stringovima može biti i katastrofalno po pitanju performansi, što ćemo videti kasnije.

XML

Rešenje koje sve češće nalazi svoju upotrebu. Ulazni string se konvertuje u XML tako što mu se na početak i kraj dodaju odgovarajući tagovi, a takođe se i delimiteri zamenjuju tagovima. Na kraju se iz dobijenog XML-a elementi jednostavno selektuju.

Videćete da ovo rešenje radi veoma dobro, ali ima jedno bitno ograničenje. Naime, ako među elementima ulaznog stringa postoji nešto što bi omelo XML konstrukciju, kao npr znakovi < ili >, upit bi pukao. Nije preporučljivo koristiti ukoliko je ulazni parametar rezultat unosa korisnika. Najčešća bezbedna upotreba je kada znate da je string nastao spajanjem brojeva, npr. od ID-eva objekata izabranih u nekoj aplikaciji.

Rekurzivni CTE

Donekle nalik rešenju sa while petljom, samo što se za “kretanje” kroz string ne koristi petlja, već rekurzivni CTE, što je uglavnom bolja varijanta. Takođe, umesto odsecanja stringa prilikom svakog prolaza kroz rekurziju, pamte se samo početna i krajnja pozicija odakle treba izvlačiti element u sledećem prolazu.

Tabela sa brojevima

Rešenje koje ima malo drugačiji pristup. Osnova upita je tabela sa brojevima od 1 do N (dužine ulaznog stringa) iz koje se radi SELECT u kombinaciji sa SUBSTRING funkcijom nad ulaznim stringom. Naime, u WHERE uslovu se za svaki broj iz tabele radi substring i proverava da li na tom mestu u ulaznom stringu postoji delimiter, a u samom selectu se opet koristeći substring u tim slučajevima izvlači odgovarajući element.

U ovakvim varijantama, često se kao tabela sa brojevima koristi prava tabela koja već postoji na serveru, ali u ovom slučaju s obzirom da smo želeli da funkcija bude samostalno funkcionalna, odlučili smo se da i same brojeve kreiramo unutar CTE-a, što je možda za neke situacije sporija varijanta, ali za neke može biti i brža.

“Tally” tabela

Ovo je varijacija na prethodnu temu. U pitanju je modifikovana verzija funkcija koje je razvio Jeff Moden za SQL Server Central. Njegove funkcije su stekle slavu zahvaljujući svojim performansama, ali su ograničene da rade samo sa NVARCHAR(4000) odnosno VARCHAR(8000) tipovima i sa delimiterom od jednog karaktera. Uprkos njegovom savetu da se to ne radi, mi smo ih ovde adaptirali da rade sa NVARCHAR(MAX) i većim delimiterom čime su malo degradirane performanse, ali videćete da je to i dalje jako dobro rešenje.

Sam naziv Tally opet označava tabelu sa brojevima, ali se ovde to malo drugačije kreira. Detalje možete naći u orignalnom članku, a ja sam probao i unutar same funkcije u komentarima da dam neka objašnjenja šta čemu služi.

Provera

Najpre ćemo prosto proveriti da li sve funkcije rade ispravno. Svakoj ćemo proslediti string ‘Miki, Paja, Šilja’

DECLARE @string NVARCHAR(MAX) = 'Miki, Paja, Šilja';
DECLARE @delimiter NVARCHAR(255) = ',';

SELECT * FROM dbo.[udfKefalo_Splitter_While] (@string, @delimiter);
SELECT * FROM dbo.[udfKefalo_Splitter_XML] (@string, @delimiter);
SELECT * FROM dbo.[udfKefalo_Splitter_CTE] (@string, @delimiter);
SELECT * FROM dbo.[udfKefalo_Splitter_Brojevi] (@string, @delimiter);
SELECT * FROM dbo.[udfKefalo_Splitter_Tally](@string, @delimiter);

Očekivano sve funkcije kao rezultat vraćaju tabelu sa tri reda. Isto će se desiti i ako probamo da upotrebimo neki složeniji delimiter:

DECLARE @string2 NVARCHAR(MAX) = 'MikixxxxPajaxxxxŠilja';
DECLARE @delimiter2 NVARCHAR(255) = 'xxxx';

Testovi

Za testiranje smo uzeli jednu privremenu tabelu i gore pomenuti string ‘Miki, Paja, Šilja’ koji ima 17 karaktera. U prvom testu ubacili smo u tabelu samo taj string 100000 puta. Zatim je nad tim podacima izvršen Select uz CROSS APPLY sa funkcijom, nalik sledećem upitu:

SELECT t.* FROM #TestPodaci AS s
CROSS APPLY [udfKefalo_Splitter_While](s.kol1, ',') AS t;

Test je izvršen za svih pet funkcija po pet puta i uzeta su prosečna vremena. U naredna 4 testa, repliciran je string svaki put za po 10 puta više, ali je i broj redova isto smanjivan za 10 puta, tako da je u petom testu bilo 10 redova sa po 170000 znakova.

U poslednjem, šestom testu, ubačen je jedan red sa milion karaktera, s obzirom da je to maksimum podržan u nekim funkcijama. Ovaj upit je izvršen samo po jednom za svaku funkciju, s obzirom da su razlike već primetne, a i u nekim slučajevima je izvršavanje trajalo više minuta.

Ukoliko vas zanima ceo testni proces, upite možete preuzeti sa ovog linka, ali ono što je bitnije – rezultati – nalaze se u tabeli ispod:

Kao što vidite, na manjem broju znakova, razlike gotovo i da nema. Na 17000 polako funkcija sa brojevima prva posustaje, a na 170000 već je osetna razlika, gde su Brojevi, ali i While već dosta slabiji. Na poslednjem testu sa milion znakova, While je već neupotrebljiv, Brojevi takođe nisu dovoljni performantni, dok se XML i Tally izdvajaju kao najbrže. CTE sve vreme drži neku „zlatnu“ sredinu.

Zaključak

Da postoji jednostavan odgovor koji metod je najbolje koristiti ne bismo ni opisivali pet funkcija, već bismo postavili samo tu jednu. Ukoliko splitujete kraće stringove, kao npr. ulazni parametar procedure, uglavnom je svejedno šta ćete koristiti. Ukoliko imate ozbiljnije zahteve, najbolje je da proverite šta najbolje radi u vašem konkretnom slučaju. To što se na mom testu neka funkcija pokazala kao bolja ili lošija ne mora da znači da će tako biti u svakoj situaciji. XML se možda pokazao malo bolje od ostalih, ali ne zaboravite na ograničenja koja sa sobom nosi.

Takođe, sve ove funkcije su napravljene tako da rade u što opštijem slučaju. Za svaku postoje načini da se unaprede. Ukoliko nemate potrebe za MAX ulaznim parametrom ili delimiterom od 256 znakova, promene na manje vrednosti će popraviti stvari. Zatim, sve funkcije vraćaju i kolonu sa rednim brojem – ukoliko vam to ne treba, mogu se uglavnom pretvoriti i u inline funkcije. U metodi sa brojevima CTE se može zameniti fizičkom tabelom, itd…

No, kao što je pomenuto na početku teksta, CLR funkcija bi trebalo da bude bolje rešenje od bilo kog T-SQL-a. U jednom od narednih tekstova, planiramo da opišemo kako se kreira CLR funkcija i to ćemo uraditi upravo na primeru spliting funkcije, pa možemo uporediti rezultate sa ovde dobijenim. U međuvremenu tu su predložene T-SQL funkcije, a ukoliko ne možete da čekate, verujem da ćete bez problema naći neku gotovu CLR funkciju – npr. funkcija Adama Machanica se posebno preporučuje kao kvalitetna.

Sve navedene funkcije možete, naravno, preuzeti iz naše sekcije Korisni Skriptovi.