What is STRING_SPLIT function?
STRING_SPLIT function is a new feature introduced in SQL Server 2016, that returns the table from a string parameter (this string parameter can be VARCHAR, NVARCHAR, CHAR and NCHAR ). This function expects two parameters -- the first one is the string and the other one is the separator. It returns a table.
Syntax
STRING_SPLIT(string,separator)
![]()
Here, the string can be of type VARCHAR, NVARCHAR, CHAR, NCHAR and a separator can be any one of the following VARCHAR(1), NVARCHAR(1), CHAR(1), NCHAR(1).
Let us write some SQL snippes to test this function. Write the following SQL snippet in SQL Server 2016.
- SELECT*FROMstring_split('Nitin Tyagi,AmitSinha,Vikas Singh',',')
Execute the preceding query and check the output.
![]()
As we can see, we have the tabular output of the comma separated string we have passed as the parameter.
Let us see, what happens if we pass NULL as the separator in the preceding example. Modify the SQL Script.
- SELECT*FROMstring_split('Nitin Tyagi,AmitSinha,VikasSingh',NULL)
Execute the preceding script and check the result.
![]()
Hence, it is clear we have to specify a separator as NCHAR(1) or NVARCHAR(1).
Let us try one more example with this function. Modify the Script as follows.
- SELECT*FROMstring_split('How are you!!',' ')
Execute the preceding query.
![]()
Well, if we have a close look at the above query, we have placed a space in the separator.
![]()
Let us check what happens if we remove the space in the separator. Modify the query and execute.
- SELECT*FROMstring_split('How are you!!','')
![]()
We have provided a space here, because removing it would throw an error, as we have to specify a separator as NCHAR(1) or NVARCHAR(1).
Summary
In this post, we saw a very new feature, i.e. STRING_SPLIT function, and how it can be used. This is very helpful for the developers now, as the same functionality can be achieved in SQL itself, instead of some programming logic in the code that would perform the same functionality.