SQL Server split Function

The split table function separates a string by a specified delimiter and returns a table.

 
-- The split table function separates a string by a specified delimiter and returns a table. Create Function split (@ string varchar (255), -- string to be split @ separator varchar (255) -- delimiter) returns @ array table (item varchar (255 )) asbegindeclare @ begin int, @ end int, @ item varchar (255) set @ begin = 1 Set @ end = charindex (@ separator, @ string, @ begin) while (@ end <> 0) beginset @ item = substring (@ string, @ begin, @ end-@ begin) insert into @ array (item) values (@ item) set @ begin = @ end + 1 Set @ end = charindex (@ separator, @ string, @ begin) endset @ item = substring (@ string, @ begin, Len (@ string) + 1-@ begin) if (LEN (@ item)> 0) insert into @ array (item) values (substring (@ string, @ begin, Len (@ string) + 1-@ begin) returnend
 

The split result is modeled after the Java string method split.

Test 1:

Select * From DBO. Split ('a, B, c, D ',',')

Or (a comma is added at the end of the string to be split)

Select * From DBO. Split ('a, B, c, d,',',')

Result:

Test 2:

Select * From DBO. Split (',A,,B, c, D ',',')

Result: