This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced an STRING_SPLIT function to split a string by passing different types of delimiters. Previously, Database developers were doing a string split operation using manual string operation. I also prepared a similar function and already shared that article.
SQL Server:Function to Split String Value Using Different Delimiters
But now with the SQL Server 2016, you do not need to execute any UDF to split your string. You can use STRING_SPLIT() and also you can pass type of your delimiters.
While executing an STRING_SPLIT, if you are getting below error, set COMPATIBILITY_LEVEL – 130 for your database.
Msg 195, Level 15, State 10, Line 2
‘STRING_SPLIT’ is not a recognized built-in function name.
|
1 |
ALTER DATABASE db_name SET COMPATIBILITY_LEVEL = 130 |
Example of STRING_SPLIT():
|
1 2 3 4 5 |
SELECT value FROM STRING_SPLIT('I am Owner of dbrnd', ' ') SELECT value FROM STRING_SPLIT('I|am|Owner|of|dbrnd', '|') |
The result:
|
1 2 3 4 5 6 7 |
value ----------------------- I am Owner of dbrnd |
