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

Deliver (feature and) regular test builds through the Google Play Store

Use case of Ansible in automating today’s industries

[Leetcode] Remove Nth Node From End of List

Building an IoT thermostat with ESP8266, Python and AWS

Distributed Computing & DevOps: Ensuring a Perfect System

Why should you adopt cloud-native architecture?

Dagger Hilt : My Beginner’s Experience

A step-by-step manual for installing NILMTK.

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

How to identify similarly pronounced words in SQL server?

Top 05 SQL Query Optimization Tips | Devstringx

SQL Query Optimization

5 Advance SQL functions you must know

How to import JSON file into SQL Server Database | Worksheet Systems Blog