How to use XML Method to Split a Single column into Multiple columns in SQL Server

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

-----------------------------------------------------
Output :

-----------------------------------------------------

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.

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 :

-----------------------------------------------------

Notes:
--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 :

Emp_ID Emp_Name
1            ABC&CDE&DE
2            FGH&HI
3           JKL&LMN&NOP
4            PQR&RS

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------