SQL Server - How to comma-separated values into different columns

This article will demonstrate the method to store comma-separated values into different columns using SQL Server(T-SQL) query.

Recently, someone asked me how they can store comma-separated values into different columns of the SQL Server database. For example, a column store both the employee's first name and last name. The requirement is to retrieve the first and last names into separate columns.

CREATE TABLE EmpNames
(
[Name] varchar(100)
)

INSERT INTO EmpNames values('Raj,Gupta')
INSERT INTO EmpNames values('Kamal,Kumar')
INSERT INTO EmpNames values('Rohan,Singh')
INSERT INTO EmpNames values('Prem,lata')

SELECT * FROM EmpNames

The quickest solution is to use the LEFT and REPLACE string function with the CHARINDEX and LEN string function.

SELECT   [Name]
        ,LEFT([Name], CHARINDEX(',', [Name]) - 1) AS [FirstName]
        ,REPLACE(SUBSTRING([Name], CHARINDEX(',', [Name]), LEN([Name])), ',', '') AS [SurName]
FROM    EmpNames