SQL SERVER – T-SQL Script to Devide One Column into Two Column

Just a day ago, we faced situation where one column in database contained two values which were separated by comma. We wanted to separate this two values in their own columns. It was interesting that value of the column was variable and something dynamic needed to be written.

Following is quick script which separates one column into two columns. The separate between two values in comma.

CREATE TABLE EMP_Demo
(EMP_PAY VARCHAR(20),
EMP_NAME VARCHAR(20),
PAY_SCALE VARCHAR(20));
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('ALPESH,7009')
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('KRUTI,9909')
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('TANMAY,16000.7')
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('NESHA,6060.8')
INSERT INTO EMP_DEMO(EMP_PAY)
VALUES ('DEVANG,14000')
UPDATE EMP_Demo
SET EMP_NAME = LEFT(EMP_PAY,CHARINDEX(',',EMP_PAY)-1)
UPDATE EMP_Demo
SET PAY_SCALE = RIGHT(EMP_PAY,LEN(EMP_PAY)-CHARINDEX(',',EMP_PAY))
SELECT *
FROM EMP_Demo
DROP TABLE EMP_Demo
GO


Reference : Pinal Dave (https://blog.sqlauthority.com)