Use of STRING_SPLIT function to split the stringCreate a user-defined table-valued function to split the string,Use XQuery to split the string value and transform a delimited string into XML
First of all, we need to create a table and insert data into it which will be used in all three methods. The table should contain a single row with field id and string with delimiter characters in it. Create a table named “student” using the following code. Insert student names separated by commas in a single row by executing the following code. Verify if data has been inserted into the table or not using the following code. Method 1: Use STRING_SPLIT function to split the string In SQL Server 2016, “STRING_SPLIT” function was introduced which can be used with compatibility level 130 and above. If you use the 2016 SQL Server version or higher you can use this build-in function. Furthermore “STRING_SPLIT” inputs a string that has delimited sub-strings and inputs one character to use as the delimiter or separator. The function outputs a single-column table whose rows contain the sub-strings. The name of the output column is “Value”. This function gets two parameters. The first parameter is a string and the second one is delimiter character or separator based on which we have to split the string. The output contains a single-column table in which substrings are present. This output column is named “Value” as we can see in the figure below. Moreover, the “STRING SPLIT” table_valued function returns an empty table if the input string is NULL.
Compatibility level of the database:
Each database is connected with a compatibility level. It enables the database’s behavior to be compatible with the particular SQL Server version it runs on. Now we will call “string_split” function to split string delimited by commas. But the compatibility level was less than 130 hence following error was raised. “Invalid object name ‘SPLIT_STRING’” Thus we need to set the database compatibility level to 130 or higher. So we will follow these step to set the compatibility level of the database.
First of all set database to “single_user_access_mode” by using the following code.
Secondly, change the compatibility level of the database by using the following code.
Put the database back to multi-user access mode by using the following code.
The output will be: Now run this code to get the required result. Output for this query will be:
Method 2: To split the string, create a user-defined table-valued function
Certainly, this traditional method is supported by all versions of SQL Server. In this technique we will create user-defined function to split the string by delimited character using “SUBSTRING” function, “CHARINDEX” and while loop. This function can be used to add data to the output table as its return type is “table”. Now execute the script below to call a split function to split string by delimiter character. The result set will be like this.
Method 3: Use XQuery to split the string value and transform a delimited string into XML
As user-defined functions are resource exhaustive so we must avoid these functions. Another option is built-in “string_split” function but this function can be used for database for which compatibility level is 130 or higher. So here comes another solution to solve this difficult task. A string can be splitted using the following XML. The output for this query will be: If you want to view the whole XML file. Click on the link. Once you have clicked the link code will look like this. Now XML string should be processed further. Finally, we will use “x-Query” to query from the XML. The output will be like this:
How to Fix the Error ‘A Network-related or Instance-specific Error occurred…How to Fix Login Failed Microsoft SQL Server Error: 18456How to Setup Database Mail in SQL Server using Gmail?Creating Clustered and Non-Clustered Indexes in SQL Server
