Earlier, I have written a blog post about how to split a single row data into multiple rows using XQuery. Today, I came across a situation where I had to split a single column data into multiple columns using delimiter.
Lets me create a sample to demonstrate the solution.
Sample :
USE TEMPDB
GO
DROP TABLE [dbo].[tbl_Employee]
GO
CREATE TABLE [dbo].[tbl_Employee](
[Employee ID] INT IDENTITY(1,1) ,
[Employee Name] VARCHAR (100) NOT NULL)
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name])
VALUES ('Andreas Berglund T')
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name])
VALUES ('Sootha Charncherngkha T')
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name])
VALUES ('Peng Wu')
GO
--Browse the data
SELECT * FROM dbo.[tbl_Employee]
GO
Solution :
Given below is the solution, where we need to convert the column into xml and then split it into multiple columns using delimiter. You can use any delimiter in the given below solution. In my sample, I used a blank space as a delimiter to split column into multiple columns.
USE TEMPDB
GO
DECLARE @delimiter VARCHAR(50)
SET @delimiter=' ' -- <=== Here, you can change the delimiter.
;WITH CTE AS
(
SELECT
[Employee ID],
[Employee Name],
CAST('' + REPLACE([Employee Name], @delimiter , '') + '' AS XML)
AS [Employee Name XML]
FROM [tbl_Employee]
)
SELECT
[Employee ID],
[Employee Name],
[Employee Name XML].value('/M[1]', 'varchar(50)') As [First Name],
[Employee Name XML].value('/M[2]', 'varchar(50)') As [Last Name],
[Employee Name XML].value('/M[3]', 'varchar(50)') As [Middle Name]
FROM CTE
GO
Let me know if you come across this situation and its solution.
SQL Server Portal 