SQL Server 2016 has introduced a built-in table valued function string_split to split a string into rows based on the separator.
Earlier, developers need to write custom/user defined function to do the split work. However, with the new function, its a built-in and can be used directly. The problem with user defined function is the way the function is defined and used.There are multiple ways usually developers write custom functions like scalar/Table valued functions. And its observed in many cases, scalar functions are not scaling as desired and causing serious performance issues.
1. Split the variable string
Select * From string_split('first,second,third',',')
2. Split the table rows with the help of CROSS APPLY
Create Table splitTable(Col int, string varchar(100)) Insert into splitTable values(1,'First,Second'),(2,'Primary,Secondary') Select * From splitTable a Cross apply string_split(a.string,',') B Drop Table splitTable
If we analyse the execution plan of the above query, we can see “Table Valued Function” operator is used to implement the function.
1. ONLY a single-character delimiters can be used at a time. If you want to apply multiple delimiter, you can use multiple CROSS APPLY.
2. There is no way to understand the ordinal position/eliminate duplicates/empty strings in the string.
3. This function will work ONLY if the compatibility of your database is 130 or later irrespective of SQL Server version.
I would recommend to use this built-in function for future development and provide your experience as part of learning and sharing.