Several Ways to Insert Split Delimited Strings in a Column

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:

USE DEMODATABASE 
GO 

CREATE TABLE EMPLOYEE 
  ( 
     ID            INT IDENTITY (1, 1), 
     EMPLOYEE_NAME VARCHAR(MAX) 
  )

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:

INSERT INTO EMPLOYEE 
            (EMPLOYEE_NAME) 
VALUES      ('DULCE , MARA , PHILIP , KATHLEEN, NEREIDA , GASTON , ETTA , EARLEAN , VINCENZA')

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

SELECT * 
FROM   EMPLOYEE

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:

USE DEMODATABASE 
GO 
CREATE TABLE EMPLOYEE_DETAIL 
  ( 
     ID      INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, 
     EMPNAME VARCHAR(MAX) NOT NULL 
  )

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.

SELECT STRING_SPLIT (STRING, SPECIALCHARACTER)

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.

DECLARE @STRING VARCHAR(MAX) 
DECLARE @SPECIALCHARACTER CHAR(1) 
SET @STRING='NISARG,NIRALI,RAMESH,SURESH' 
SELECT * 
FROM   STRING_SPLIT (@STRING, ',')

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:

   DECLARE @EMPLOYEENAME VARCHAR(MAX) 
   SET @EMPLOYEENAME =(SELECT EMPLOYEE_NAME 
                       FROM   EMPLOYEE)

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:

   DECLARE @SEPARATOR CHAR(1) 
   SET @SEPARATOR=','

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:

   INSERT INTO EMPLOYEE_DETAIL 
               (EMPNAME) 
   SELECT * 
   FROM   STRING_SPLIT(@EMPLOYEENAME, @SEPARATOR)

The following is the entire script:

DECLARE @EMPLOYEENAME VARCHAR(MAX) 

SET @EMPLOYEENAME =(SELECT EMPLOYEE_NAME 
                    FROM   EMPLOYEE) 
DECLARE @SEPARATOR CHAR(1) 
SET @SEPARATOR=',' 
INSERT INTO EMPLOYEE_DETAIL 
            (EMPNAME) 
SELECT * 
FROM   STRING_SPLIT(@EMPLOYEENAME, @SEPARATOR)

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:

SELECT * 
FROM   EMPLOYEE_DETAIL

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:

USE demodatabase 
go 

DECLARE @xml       AS XML, 
        @QueryData AS VARCHAR(max), 
        @delimiter AS VARCHAR(10) 

SET @QueryData=(SELECT employee_name 
                FROM   employee) 
SET @delimiter =',' 
SET @xml = Cast(( '<EMPNAME>' 
                  + Replace(@QueryData, @delimiter, '</EMPNAME><EMPNAME>') 
                  + '</EMPNAME>' ) AS XML) 

SELECT @XML

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:

<EMPNAME>DULCE </EMPNAME>
<EMPNAME> MARA </EMPNAME>
<EMPNAME> PHILIP </EMPNAME>
<EMPNAME> KATHLEEN</EMPNAME>
<EMPNAME> NEREIDA </EMPNAME>
<EMPNAME> GASTON </EMPNAME>
<EMPNAME> ETTA </EMPNAME>
<EMPNAME> EARLEAN </EMPNAME>
<EMPNAME> VINCENZA</EMPNAME>

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

USE DEMODATABASE 
GO 

DECLARE @XML       AS XML, 
        @STR       AS VARCHAR(MAX), 
        @DELIMITER AS VARCHAR(10) 

SET @STR=(SELECT EMPLOYEE_NAME 
          FROM   EMPLOYEE) 
SET @DELIMITER =',' 
SET @XML = CAST(( '<EMPNAME>' 
                  + REPLACE(@STR, @DELIMITER, '</EMPNAME><EMPNAME>') 
                  + '</EMPNAME>' ) AS XML) 

SELECT N.VALUE('.', 'VARCHAR(10)') AS VALUE 
FROM   @XML.NODES('EMPNAME') AS T(N)

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:

USE DEMODATABASE
GO
DECLARE @XML AS XML,@STR AS VARCHAR(MAX),@DELIMITER AS VARCHAR(10)
SET @STR=(SELECT EMPLOYEE_NAME FROM EMPLOYEE)
SET @DELIMITER =','
SET @XML = CAST(('<EMPNAME>'+REPLACE(@STR,@DELIMITER ,'</EMPNAME><EMPNAME>')+'</EMPNAME>') AS XML)
INSERT INTO EMPLOYEE_DETAIL (EMPNAME)
SELECT N.VALUE('.', 'VARCHAR(10)') AS VALUE FROM @XML.NODES('EMPNAME') AS T(N)
/*Output
 (9 rows affected)
 */

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

USE DEMODATABASE 
GO 
SELECT * 
FROM   EMPLOYEE_DETAIL

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:

REATE FUNCTION [DBO].SPLIT_DELIMITED_STRING (@SQLQUERY  VARCHAR(MAX), 
                                              @DELIMITOR CHAR(1)) 
RETURNS @RESULT TABLE( 
  VALUE VARCHAR(MAX)) 
AS 
  BEGIN 
      DECLARE @DELIMITORPOSITION INT = CHARINDEX(@DELIMITOR, @SQLQUERY), 
              @VALUE             VARCHAR(MAX), 
              @STARTPOSITION     INT = 1 

      IF @DELIMITORPOSITION = 0 
        BEGIN 
            INSERT INTO @RESULT 
            VALUES     (@SQLQUERY) 

            RETURN 
        END 

      SET @SQLQUERY = @SQLQUERY + @DELIMITOR 

      WHILE @DELIMITORPOSITION > 0 
        BEGIN 
            SET @VALUE = SUBSTRING(@SQLQUERY, @STARTPOSITION, 
                         @DELIMITORPOSITION - @STARTPOSITION) 

            IF( @VALUE <> '' ) 
              INSERT INTO @RESULT 
              VALUES     (@VALUE) 

            SET @STARTPOSITION = @DELIMITORPOSITION + 1 
            SET @DELIMITORPOSITION = CHARINDEX(@DELIMITOR, @SQLQUERY, 
                                     @STARTPOSITION) 
        END 

      RETURN 
  END

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

DECLARE @SQLQUERY NVARCHAR(MAX) 
SET @SQLQUERY=(SELECT EMPLOYEE_NAME 
               FROM   EMPLOYEE) 
INSERT INTO EMPLOYEE_DETAIL 
SELECT * 
FROM   SPLIT_DELIMITED_STRING(@SQLQUERY, ',')

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.

Tags: sql functions, t-sql Last modified: September 15, 2022