Journey to SQL Authority with Pinal Dave

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 (http://www.SQLAuthority.com)