T-SQL Script to Divide One Column Data into Two Columns

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')

created a table and inserted some sample data for verifying the this script.

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

GO

DROP TABLE EMP_Demo

GO

In some situations, one column in database contains two values which were separated by comma. In this article we separates this two values in their own columns.

Related Posts

1. SQL Tutorial : Converting Rows to Columns
2. SQL TIPS : Writing Multiple Column Subqueries
3. Error: SQL0161N Insert or Update operation does not conform to the view definition
4. Encryption and Decryption in SQL Server 2005
5. ORALOAD Oracle Utility for Bulk Data Load

Tags: Divide Columns, Split Columns, Split Columns in T-SQL, SQL Server, T-SQL