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://blog.SQLAuthority.com)
pinaldave
Great job, i am learning the sql server and a big fan of the person who are doing good job in the programming field
is there any way that able to do this?
i mean within the select statement.
It is good…
here CHARINDEX is just counting the position of the field. And thus creating the whole formula to provide the solution..
Good job Pinal
Hi,
good demo of CHARINDEX.
I want to add a column ‘DEPT’ after ‘EMP_NAME’ column before ‘PAY_SCALE’ in EMP_Demo table.
I want to add it using SQL statement in 2005.
how to do it?
Thanks a lot in advance.
Hea thanks a million love your site.
What do you do with this query if you have more then two colunms of data though?>
I’m trying to implement this when using address.
So i never know how many rows would be needed. but
say could have 4 or 5 or 6 bits of data seperated by each coma that i would like to see in one cell.
‘No 10, Drumhaughley, Killoe, Co. Longford’ For example?
thanks
@Eibhlin
This could be easily implemented.
I do have a question, lets look an example.
This is your format,
Apt NO, Building NO, Street Address, City Name, State, Zipcode
Ex: Apt #14,5233,Niagara falls,NewYork City, New York,12345
If format is like above, There is no issue, I can easily place all the 6 values in 6 different columns, but what if we get this value,
5233,Niagara falls,NewYork City, New York,12345
How I am I Suppose to know ? that there is no Apt No in above example, I might start putting values in wrong column ?
How do you deal with this, Like if you dont have any value, do you display it as empty, like this,
,5233,Niagara falls,NewYork City, New York,12345
Solution :
Check this link :
http://dhameliya.blogspot.com/2009/04/how-to-split-comma-separated-string-in.html
~ IM.