XML Method to Split a Single column into Multiple columns in SQL Server
Suppose We have a Table called Tbl_EMP with columns Emp_Id , Emp_Name where the EMP_Name have the FirstName,MiddleName and LastName combined by the delimiter "_" underscore as follows..
Select*From Tbl_EMP;
GO
Emp_ID Emp_Name
1 ABC_CDE_DE
2 FGH_HI
3 JKL_LMN_NOP
4 PQR_RS
Now if we wants to Split this Name into 3 Columns as First_Name,Middle_Name and Last_Name , we can do it as follows using the XML Method :
DECLARE @Delimiter VARCHAR(50)
SET @Delimiter='_'; -- <=== Here, you can change the delimiter.
WITH CTE AS
(
SELECT
[Emp_ID],
[Emp_Name],
CAST('<D>' + REPLACE([Emp_Name], @Delimiter , '</D><D>') + '</D>' AS XML)
AS [Emp_Name XML]
FROM Tbl_EMP )
SELECT
[Emp_ID],
[Emp_Name],
[Emp_Name XML].value('/D[1]', 'varchar(50)') As [First_Name],
[Emp_Name XML].value('/D[2]', 'varchar(50)') As [Middle_Name],
[Emp_Name XML].value('/D[3]', 'varchar(50)') As [Last_Name]
FROM CTE
GO
If you observe the above Output, which may not looks accurate for records 2 and 4, as they don't have the Middle Name which should be Last Name.
Suppose We have a Table called Tbl_EMP with columns Emp_Id , Emp_Name where the EMP_Name have the FirstName,MiddleName and LastName combined by the delimiter "_" underscore as follows..
Select*From Tbl_EMP;
GO
Emp_ID Emp_Name
1 ABC_CDE_DE
2 FGH_HI
3 JKL_LMN_NOP
4 PQR_RS
Now if we wants to Split this Name into 3 Columns as First_Name,Middle_Name and Last_Name , we can do it as follows using the XML Method :
DECLARE @Delimiter VARCHAR(50)
SET @Delimiter='_'; -- <=== Here, you can change the delimiter.
WITH CTE AS
(
SELECT
[Emp_ID],
[Emp_Name],
CAST('<D>' + REPLACE([Emp_Name], @Delimiter , '</D><D>') + '</D>' AS XML)
AS [Emp_Name XML]
FROM Tbl_EMP )
SELECT
[Emp_ID],
[Emp_Name],
[Emp_Name XML].value('/D[1]', 'varchar(50)') As [First_Name],
[Emp_Name XML].value('/D[2]', 'varchar(50)') As [Middle_Name],
[Emp_Name XML].value('/D[3]', 'varchar(50)') As [Last_Name]
FROM CTE
GO
-----------------------------------------------------
We can handle this Using the Case Statement in above query as follows :
DECLARE @Delimiter VARCHAR(50)
SET @Delimiter='_'; -- <=== Here, you can change the delimiter.
WITH CTE AS
(
SELECT
[Emp_ID],
[Emp_Name], CAST('<D>' + REPLACE([Emp_Name], @Delimiter , '</D><D>') + '</D>' AS XML)
AS [Emp_Name XML] FROM Tbl_EMP )SELECT
[Emp_ID],
[Emp_Name],
[Emp_Name XML].value('/D[1]', 'varchar(50)') As [First_Name],
CASE
WHEN ([Emp_Name XML].value('/D[2]', 'varchar(50)') IS NOT NULL)
AND ([Emp_Name XML].value('/D[3]', 'varchar(50)') IS NULL)
THEN NULL
ELSE [Emp_Name XML].value('/D[2]', 'varchar(50)')
END AS [Middle_Name],
CASE
WHEN ([Emp_Name XML].value('/D[3]', 'varchar(50)')) IS NULL
THEN [Emp_Name XML].value('/D[2]', 'varchar(50)')
ELSE [Emp_Name XML].value('/D[3]', 'varchar(50)')
END AS [Last_Name]
FROM CTE
GO
-----------------------------------------------------
Output :
SET @Delimiter='_'; -- <=== Here, you can change the delimiter.
WITH CTE AS
(
SELECT
[Emp_ID],
[Emp_Name], CAST('<D>' + REPLACE([Emp_Name], @Delimiter , '</D><D>') + '</D>' AS XML)
AS [Emp_Name XML] FROM Tbl_EMP )SELECT
[Emp_ID],
[Emp_Name],
[Emp_Name XML].value('/D[1]', 'varchar(50)') As [First_Name],
CASE
WHEN ([Emp_Name XML].value('/D[2]', 'varchar(50)') IS NOT NULL)
AND ([Emp_Name XML].value('/D[3]', 'varchar(50)') IS NULL)
THEN NULL
ELSE [Emp_Name XML].value('/D[2]', 'varchar(50)')
END AS [Middle_Name],
CASE
WHEN ([Emp_Name XML].value('/D[3]', 'varchar(50)')) IS NULL
THEN [Emp_Name XML].value('/D[2]', 'varchar(50)')
ELSE [Emp_Name XML].value('/D[3]', 'varchar(50)')
END AS [Last_Name]
FROM CTE
GO
-----------------------------------------------------
Output :
-----------------------------------------------------
Notes:
--In the above query, for the XML Tags <D>;</D>, you can use any other letter like <T>;</T>.
--In the above query, for the XML Tags <D>;</D>, you can use any other letter like <T>;</T>.
--[Emp_Name XML].value('/D[1]' is the First Part of String with Delimiter...
--You can use the delimiter based on your source data.
-----------------------------------------------------
You can change/update the delimiter in the Source Table Column as follows :
Update Tbl_EMP
Set Emp_Name = REPLACE(Emp_Name, '_', '&')
Output :
Set Emp_Name = REPLACE(Emp_Name, '_', '&')
Output :
Emp_ID Emp_Name
1 ABC&CDE&DE
2 FGH&HI
3 JKL&LMN&NOP
4 PQR&RS
1 ABC&CDE&DE
2 FGH&HI
3 JKL&LMN&NOP
4 PQR&RS
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.