However, STRING_SPLIT does not maintain the order of the parts, which can be an issue if the order matters. It also doesn’t support multi-character separators and might not handle extra separators at the start or end of the string correctly. In these situations, custom functions or other methods might be needed to split the string accurately. In this article, we will discuss several ways to split a delimited string value through multiple methods, including:
Using the STRING_SPLIT function to split the stringCreating a user-defined table-valued function to split the stringUsing XQuery to split the string value and transform a delimited string into XML
First, 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 fields ID and student_name, where the string includes delimiter characters. 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 whether the data has been inserted into the table by using the following code:
1. Use STRING_SPLIT Function to Split the String
An easy way to split strings in SQL Server is by using the STRING_SPLIT function. This feature breaks a string into separate values based on a chosen single-character separator and returns them as rows in a table. It’s simple to use, but remember that STRING_SPLIT doesn’t keep the order of the parts, so you might need to sort them if the order is important. This method is great for handling basic separated data without needing complex queries. The STRING_SPLIT function takes a string containing delimited substrings and uses one character as the delimiter or separator. It outputs a single-column table where each row contains a substring. The name of the output column is Value. This function accepts two parameters: the first is a string, and the second is a delimiter character or separator based on which the string is split. The output column is named “Value” as shown 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 associated with a compatibility level, allowing it to behave consistently with the specific SQL Server version it runs on. Now, we will call the “STRING_SPLIT” function to split a string delimited by commas. Since the compatibility level was less than 130, the following error was raised: “Invalid object name ‘SPLIT_STRING’”. Thus, we need to set the database compatibility level to 130 or higher. Follow these steps to set the compatibility level of the database:
First, set the database to “single_user access mode” using the following code.
Next, change the database’s compatibility level using the following code.
Switch the database back to multi-user access mode using the following code.
The output will be: Now run this code to get the required result: The output for this query will be:
2. To Split the String, Create a User-Defined Table-Valued Function
When functions like STRING_SPLIT can’t handle complex separators or keep the order of items, creating a custom table-valued function can help. This method gives you control over how you split the string, supports multi-character separators, keeps items in order, and manages issues like leading or trailing separators. Using a custom function ensures the results meet your needs, making it a flexible option for splitting strings in SQL Server. 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 the split function to break the string by the delimiter character: The result set will look like this:
3. Use XQuery to Split the String Value and Transform a Delimited String into XML
Turning a delimited string into XML using XQuery is a structured way to work with the data. By changing the string into XML, each part between the delimiters becomes a separate XML element. This method gives better control over complex delimiters, supports multi-character delimiters, and keeps the order of elements. It is a strong option for precise string splitting in SQL Server. A string can be split using the following XML approach. The output for this query will be: To view the entire XML file, click on the link provided. Once opened, the code will look like this: Next, the XML string should be processed further. Finally, we will use “XQuery” to query from the XML: The output will be like this:
