Several Ways to Insert Split Delimited Strings in a Column

  1. Create a user-defined table-valued function to split the string and insert it into the table.
  2. Split the string using STRING_SPLIT function and insert the output into a table.
USE DEMODATABASE 
GO
CREATE TABLE EMPLOYEE
(
ID INT IDENTITY (1, 1),
EMPLOYEE_NAME VARCHAR(MAX)
)
INSERT INTO EMPLOYEE 
(EMPLOYEE_NAME)
VALUES ('DULCE , MARA , PHILIP , KATHLEEN, NEREIDA , GASTON , ETTA , EARLEAN , VINCENZA')
SELECT * 
FROM EMPLOYEE
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.

SELECT STRING_SPLIT (STRING, SPECIALCHARACTER)
DECLARE @STRING VARCHAR(MAX) 
DECLARE @SPECIALCHARACTER CHAR(1)
SET @STRING='NISARG,NIRALI,RAMESH,SURESH'
SELECT *
FROM STRING_SPLIT (@STRING, ',')
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)
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)
SELECT * 
FROM EMPLOYEE_DETAIL

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.

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
<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>
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)
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)
*/
USE DEMODATABASE 
GO
SELECT *
FROM EMPLOYEE_DETAIL

Method 3: Split string using a 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 that will use while loop and CHARINDEX and SUBSTRING function.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
{coding}Sight

{coding}Sight

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/