Several Ways to Insert Split Delimited Strings in a Column

  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.
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

What is STRING_SPLIT Function

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

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

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Automation Scripts for WordPress — Part 1 EasyEngine

Introducing C2 Object Storage

Spring Boot with Alibaba Cloud Object Storage Service

Installing RabbitMQ on Windows

Learning Web Development

Animated Background with Pure CSS and Html

Five Developers’ Pain Points of E-Commerce Localization

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/

More from Medium

Cast() and Convert() Functions in SQL Server

Triggers in SQL and What is DML Trigger

REGEX in SQL

SQL Query optimization Techniques