How to split a row into several rows by delimiters in a SQL Server table

Introduction

This script is used to split a string into strings by delimiters. The script will demonstrate the process by creating a test table and a stored procedure. We can see from the result of the original table and the final table.

Scenarios

As some people asked how to split a string in a row into multiple strings and put the strings into rows, this script will provide some help. You will create your own with the reference of this script.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
2. Select the specified database and create a "New Query", copy the code from SplitStrings.sql, paste it.
After the script runs, we'll get the following figure:

Here are some code snippets for your references.

IF (OBJECT_ID('splitstring','TF') IS NOT NULL) DROP FUNCTION splitstring
GO
CREATE FUNCTION splitstring
(@string nvarchar(400),
 @delimiter nvarchar(5)
)
RETURNS @returntable table (Num int IDENTITY(1,1),EMPCode nvarchar(50))
AS
BEGIN
    DECLARE @xml xml
    SET @xml = '<h>'+REPLACE(@string,@delimiter,'</h><h>')+'</h>'


    INSERT INTO @returntable(EMPCode)
    SELECT  c.value('.','nvarchar(50)')    
    FROM @xml.nodes('/h') T(c)


    RETURN
END
GO

IF (OBJECT_ID('splitstring','TF') IS NOT NULL) DROP FUNCTION splitstring
GO
CREATE FUNCTION splitstring
(@string nvarchar(400),
 @delimiter nvarchar(5)
)
RETURNS @returntable table (Num int IDENTITY(1,1),EMPCode nvarchar(50))
AS
BEGIN
    DECLARE @xml xml
    SET @xml = '<h>'+REPLACE(@string,@delimiter,'</h><h>')+'</h>'


    INSERT INTO @returntable(EMPCode)
    SELECT  c.value('.','nvarchar(50)')    
    FROM @xml.nodes('/h') T(c)


    RETURN
END
GO

SQL Server 2005 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.