Several Ways to Insert Split Delimited Strings in a Column

Several Ways to Insert Split Delimited Strings in a Column
3.5 (70%) 4 vote[s]

In this article, I will demonstrate several ways to split the delimited string and insert it in into a column of a table in SQL Server. You can do it using the following methods:

  1. Convert delimited string into XML, use XQuery to split the string, and save it into the table.
  2. Create a user-defined table-valued function to split the string and insert it into the table.
  3. Split the string using STRING_SPLIT function and insert the output into a table.

To demonstrate the above methods, let me prepare a demo setup. First, let us create a table named Employee on DemoDatabase. To do that, we need to execute the following query:

For this demo, we will insert the names of all employees in one row and the names of employees will be separated by a comma. To do that, we need to execute the following query:

Execute the following query to verify that data has been inserted into the column.

The following is the output:

As I mentioned above, we are going split the delimited string and insert it into a table. So, we will create a table named Employee_Detail to store the delimited string split by any of the above methods.

To create a table, execute the following code:

Method 1: Use STRING_SPLIT function to split the delimited string

We will use the STRING_SPLIT function to split the string in a column and insert it into a table. Before we do that, let me explain about the STRING_SPLIT function.

What is STRING_SPLIT Function

STRING_SPLIT is a table-valued function, introduced in SQL Server 2016. This function splits the string based on the special character within the row and returns the output in a separate table. We can use this function on the databases that have compatibility level equal to or higher than 130.

The STRING_SPLIT function accepts two parameters and returns a table with the separated values. The following is the syntax of the STRING_SPLIT function.

In the above syntax, SPECIALCHARACTER is one character which will be used to separate the input string.

The following is a simple example of the STRING_SPLIT function.

The following is an output of the query:

As you can see in the above example, the name of the output column returned by STRING_SPLIT is “value.” We can filter the output returned by the function using the WHERE clause on the “value” column and also, we can sort the order of output using the ORDER BY clause on the “value” column.

The following is an example.

Now to insert a delimited string into a table, we will perform the following tasks:

  1. Create a variable named @EmployeeName, which holds the output of the Employee table. To do that, execute the following code:
  2. Create another variable called @Separator of the char data type. This variable holds the value of the separator, which will be used to split the strings into multiple values. To create the variable and assign the value to the separator, execute the following code:
  3. Now use the “STRING_SPLIT” function to split the values of the employee_name column of the Employee table and insert the values into the EMPLOYEENAME table. To do that, execute the following code:

The following is the entire script:

Execute the above script. The script will insert nine rows into the table. Once you execute it, make sure the data has been inserted into the EMPLOYEENAME table. For this, execute the following query:

The following is the output:

Method 2: Split string using XML and insert the output in the table

When we want to split the delimited string, we can do it using table-valued functions. As we know, the user-defined table-valued functions are resource-intensive and should be avoided. In such cases, we do not have many options available. As I mentioned, the STRING_SPLIT function can be used for the databases which have compatibility level greater than or equal to 130. In such circumstances, it is difficult to find a way to split a delimited string. We have created a simple and efficient solution for this task. We can split the string using XML.

So, in this section, I am going to explain the code of XML which can be used to insert the split delimited string in different rows of a column.

I have split the entire code into three steps.

Step 1: Convert the delimited string into the XML Format. To do that, execute the following code:

The following is the output:

To view the entire XML string, click the cell as shown on the image above. Once you click the cell, the XML file should look like following:

Step 2: Once the string is converted into XML, use X-Query to query the XML file. To do that, execute the following code:

The following is the output:

Step 3: Insert the output generated by the query executed above into the Employee_Detail table. To do that, execute the following code:

Once data is inserted, execute the following script to verify that the data has been inserted. Execute the following query:

The following is the output.

Method 3: Split string using table-valued function and insert the output of the function in the table

This approach is traditional, and is supported in all versions and editions of SQL Server. In this approach, we will create a user-defined table-valued function which will use while loop and CHARINDEX and SUBSTRING function.

The following is the code to create a function:

Once the function is created, execute the following query to split the query and insert the output into the Employee_Detail table.

Once data is inserted into the table, execute the following query to verify that data has been inserted properly

Summary

In this article, I have covered:

  1. Different approach to split and insert the delimited string in table.
  2. High level is summary of STRING_SPLIT function.
  3. Split and insert a delimited string using XML and XQuery.
  4. Split and insert delimited string using a user-defined table-valued function.

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.
Nisarg Upadhyay