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.
hi friends,
i have one table is normal column is bit
id isnormal
1 0
2 0
3 0
4 1
5 1
6 0
7 1
i wnat oput put like this
id normal abnormal
1 0 1
2 0 1
3 0 1
4 1 0
5 1 0
6 0 1
7 1 0
note:–where isnormal is 0 that time i wnat to display 1 in abnormal column
where isnormal is 1 that time i wnat to display 1 in normal column
first i want to divide is normal column into two columns one is
norman
second one is
abnormal
please help me it’s very urgent in my project
Thanks & Regards’
PrasadGopathi
Hi Prasad,
CREATE TABLE status(id INT, isnormal INT, normal INT,abnormal INT)
GO
INSERT INTO status(id , isnormal)
SELECT 1,0
UNION ALL
SELECT 2,0
UNION ALL
SELECT 3,0
UNION ALL
SELECT 4,1
UNION ALL
SELECT 5,1
UNION ALL
SELECT 6,0
UNION ALL
SELECT 7,1
SELECT * FROM status
UPDATE status
SET normal = (isnormal)
UPDATE status
SET abnormal = (1-isnormal)
SELECT id,normal,abnormal FROM status
I think It is your answer.
Thanks And Regards
Rahul Bhargava
@prasad
The clearest solution is to use a CASE statement.
WITH
Data(id, isnormal)
AS
(
SELECT 1, 0 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 5, 1 UNION ALL
SELECT 6, 0 UNION ALL
SELECT 7, 1
)
SELECT
Id,
IsNormal,
CASE IsNormal
WHEN 0 THEN 1
ELSE 0
END Abnormal
FROM
Data;
Though, a mathematical solution could also be used, but not be as clear. Here is one: (IsNormal + 1) % 2
I need to create add some values in table. where only column value is going to be changed . is there a way to add multiple rows in single oracle statment
for an instance I have table like
Company ,Area, EmployeeNo
In this table company and area field is going to be same and only employee No is going tobe changed and I have employee no in the below format
1234,4567,7890
and I am expexting an out put as below
Company Area Employee No
Abc US 1234
Abc US 4567
Abc US 7890
is there a way to execute in single statment.