Introduction

This T-SQL script will demo how to split comma-delimited string column into multiple records in SQL Server.

Scenarios

As many people asked how to split comma-delimited string column into multiple records in SQL Server, we create a table for demonstrating.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
2. Copy the code from SplitString.sql, paste it in a new query and run the script.

After the script finishes running, we'll get the following figure:

Here are some code snippet for your reference:

IF OBJECT_ID('Split','U') IS NOT NULL DROP TABLE Split


CREATE TABLE Split
(column1 char(5),
column2 varchar(500))


INSERT INTO Split VALUES
('a','a,b,c,d,e,f'),
('b','b,c,d,e,f'),
('c','c,d,e,f'),
('d','d'),
('e','e,'),
('f',',f')


ALTER TABLE Split ADD RowNumber INT IDENTITY(1,1)
--the original table
SELECT * FROM Split

IF OBJECT_ID('Split','U') IS NOT NULL DROP TABLE Split


CREATE TABLE Split
(column1 char(5),
column2 varchar(500))


INSERT INTO Split VALUES
('a','a,b,c,d,e,f'),
('b','b,c,d,e,f'),
('c','c,d,e,f'),
('d','d'),
('e','e,'),
('f',',f')


ALTER TABLE Split ADD RowNumber INT IDENTITY(1,1)
--the original table
SELECT * FROM Split

Prerequisites

SQL Server 2008 or higher version

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.