{"id":4788,"date":"2016-03-13T00:27:10","date_gmt":"2016-03-12T18:57:10","guid":{"rendered":"http:\/\/sqlhints.com\/?p=4788"},"modified":"2016-03-13T00:30:39","modified_gmt":"2016-03-12T19:00:39","slug":"string_split-function-in-sql-server-2016","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2016\/03\/13\/string_split-function-in-sql-server-2016\/","title":{"rendered":"STRING_SPLIT function in Sql Server 2016"},"content":{"rendered":"

STRING_SPLIT<\/strong> is one of the new built-in table valued function introduced in Sql Server 2016. This table valued function splits the input string by the specified character separator and returns output as a table.<\/p>\n

SYNTAX:<\/strong><\/p>\n

\r\nSTRING_SPLIT (string, separator)\r\n<\/pre>\n
Where string <\/strong><\/em>is a character delimited string of type CHAR, VARCHAR, NVARCHAR and NCHAR.
\nSeparator <\/strong><\/em>is a single character delimiter by which the input string need to be split. The separator character can be one of the type: CHAR(1), VARCHAR(1), NVARCHAR(1) and NCHAR(1).
\nResult of this function is a table with one column with column name as value.<\/p>\n
Let us understand this function with extensive list of examples:<\/P><\/p>\n
EXAMPLE 1:<\/strong> This example shows how we can use STRING_SPLIT function to splits the comma separated string.<\/P><\/p>\n
\r\nSELECT * \r\nFROM STRING_SPLIT('Basavaraj,Kalpana,Shree',',')\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Sql<\/a><\/p>\n
EXAMPLE 2:<\/strong> In this example, passing string and separator parameters as variables.<\/p>\n
\r\nDECLARE @string VARCHAR(100) = 'Basavaraj,Kalpana,Shree',\r\n        @separator CHAR(1) =',' \r\nSELECT * \r\nFROM STRING_SPLIT(@string,@separator)\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Sql<\/a><\/p>\n
Let us execute the following statement which is moving the split result into a temp table. After inserting the split result into the temp table we are verifying the split result column type by using the sp_columns system stored procedure.<\/p>\n
\r\nDECLARE @string VARCHAR(100) = 'Basavaraj,Kalpana,Shree',\r\n        @separator CHAR(1) =',' \r\nSELECT *\r\n\tINTO #TempTable\r\nFROM STRING_SPLIT(@string,@separator)\r\nGO\r\nTEMPDB..sp_Columns #TempTable\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Sql<\/a><\/p>\n
From the result we can conclude that the size of the value column in the table returned by the STRING_SPLIT function will be same as that of the string which needs to split and type will be VARCHAR if the string which needs to be split is of type CHAR or VARCHAR.<\/p>\n
EXAMPLE 3:<\/strong> This example demonstrates that the separator can be of only one character.<\/p>\n
\r\nDECLARE @string VARCHAR(100) = 'Basavaraj#@Kalpana#@Shree',\r\n        @separator CHAR(2) ='#@' \r\nSELECT * FROM STRING_SPLIT(@string,@separator)\r\n<\/pre>\n
RESULT:<\/strong><\/p>\n
Msg 214, Level 16, State 11, Line 3
\nProcedure expects parameter ‘separator’ of type ‘nchar(1)\/nvarchar(1)’.<\/p>\n
Let us re-try this example by using a single character separator instead of two characters<\/p>\n
\r\nDECLARE @string VARCHAR(100) = 'Basavaraj#@Kalpana#@Shree',\r\n        @separator CHAR(2) ='@' \r\nSELECT * FROM STRING_SPLIT(@string,@separator)\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Sql<\/a><\/p>\n
From the above example results it is clear that the separator should always be a single character of any one of the data type CHAR(1), VARCHAR(1), NVARCHAR(1) and NCHAR(1).<\/p>\n
EXAMPLE 4:<\/strong> In this example both the string which needs to be split and the separator are double byte characters.<\/p>\n
\r\nDECLARE @string NVARCHAR(100) = N'\u4f60\u597d\u4e2d\u4f60\u597d',\r\n        @separator NCHAR(1) =N'\u4e2d' \r\nSELECT * FROM STRING_SPLIT(@string,@separator)\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Sql<\/a><\/p>\n
Let us execute the following statement which is moving the split result into a temp table. After inserting the split result into the temp table we are verifying the split result column type by using the sp_columns system stored procedure.<\/p>\n
\r\nDECLARE @string NVARCHAR(100) = N'\u4f60\u597d\u4e2d\u4f60\u597d',\r\n        @separator NCHAR(1) =N'\u4e2d' \r\nSELECT * \r\n\tINTO #TempTbl\r\nFROM STRING_SPLIT(@string,@separator)\r\nGO\r\nTEMPDB..sp_Columns #TempTbl\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Sql<\/a><\/p>\n
From the result we can conclude that the size of the value column in the table returned by the STRING_SPLIT function will be same as that of the string which needs to split and type will be NVARCHAR if the string which needs to be split is of type NCHAR or NVARCHAR.<\/p>\n
EXAMPLE 5:<\/strong> This example explains the behavior of the STRING_SPLIT function if the parameters are null<\/p>\n
Let us execute the following statement where the Separator is NULL<\/p>\n
\r\nSELECT * FROM STRING_SPLIT('Basavaraj,Kalpana,Shree',NULL)\r\n<\/pre>\n
RESULT:<\/strong><\/p>\n
Msg 214, Level 16, State 11, Line 1
\nProcedure expects parameter ‘separator’ of type ‘nchar(1)\/nvarchar(1)’.<\/p>\n
Let us execute the following statement where the string which needs to be split is NULL.<\/p>\n
\r\nSELECT * FROM STRING_SPLIT(NULL,',')\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Sql<\/a><\/p>\n
EXAMPLE 6:<\/strong> In this example we are splitting the string and the resultant table is joined with another table<\/p>\n
Let us create a customer table with sample records by executing the following script<\/p>\n
\r\n--Create customer table\r\nCREATE TABLE Customer \r\n( CustomerId INT IDENTITY (1, 1) NOT NULL ,\r\n  FirstName NVARCHAR(50), LastName NVARCHAR(50))\r\nGO\r\n--Insert 3 sample records into the customer table \r\nINSERT INTO dbo.Customer ( FirstName, LastName )\r\nVALUES ('Basavaraj','Biradar'),\r\n       ('Kalpana','Patil'),\r\n       ('Shree','Biradar')\r\n<\/pre>\n
Execute the below state to split the string and the resultant table is joined with another table<\/p>\n
\r\nSELECT *\r\nFROM dbo.Customer C \r\n\t\tINNER JOIN STRING_SPLIT('Basavaraj,Shree',',') SF\r\n\t\t\tON C.FirstName = SF.value\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Sql<\/a><\/p>\n
EXAMPLE 7:<\/strong> This example explains the behavior of the STRING_SPLIT function if the string to be split is an empty string<\/p>\n
\r\nSELECT * FROM STRING_SPLIT('',',')\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Sql<\/a><\/p>\n
EXAMPLE 8:<\/strong> This example shows the behavior of the STRING_SPLIT function if the string to be split is terminated by the separator character<\/p>\n
\r\nSELECT * FROM STRING_SPLIT('Basavaraj,Shree,',',')\r\n<\/pre>\n
RESULT: <\/strong>
\n\"Sql<\/a>
\n[ALSO READ]<\/strong><\/p>\n\n\n\n\n
SQL SERVER 2016<\/strong><\/td>\n<\/tr>\n
\n