There is a magnificent bug in string_split !ĭECLARE VARCHAR ( 50 ) = 'John, Jeremy, Jack' Just create a select statement appending UNION ALL to each one of the item in the array Here is the table which is going to contain the rows of each item in the String array Set = 'First item,Second item,Third item' Here is another way: - Here is the String Array you want to convert to a Table Providing it with a string to be separated and a comma as a separator: The below simple straight-forward call for the STRING_SPLIT function on my database, In my case, my local SQL Server instance was upgradedįrom SQL Server 2014 version to SQL Server 2016 version. Database Compatibility Level Error for STRING_SPLIT Functionīefore using the STRING_SPLIT function, you should make sure that the databaseĬompatibility level is 130. Length of the value column will be the same as the length of the string that willīe split. If the input stringĭata type is nvarchar or nchar, the value column data type will be nvarchar. The default data type of the value column is varchar. STRING_SPLIT returns a single column table. The separator is a single character that will be used to separate theĬoncatenated input string, with char, nchar, varchar or nvarchar data types. The string is an expression of characters with char, nchar, varchar or nvarcharĭata types. STRING_SPLIT function takes two parameters: The output separated values in the form of table, with a row for each delimited That splits the provided input string by a specified separation character and returns SQL Server 2016 introduced a new built-in table-valued function, STRING_SPLIT What is hidden for us in SQL Server 2016 to achieve this task more efficiently? Through it, searching for the delimiter and retrieve a table with these separated We used to write such user defined functions that takes a string, loops
Value separately in your SQL Server code or provide this normalized data to anotherĭata source. With this de-normalized data, you may need to normalize it to work with each single
By: Ahmad Yaseen | Updated: | Comments (14) | Related: More > Functions Systemĭuring your experience as a SQL Server Database Administrator, you may work withĪpplications that store data as comma-separated values in a single column.