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)
17 Comments. Leave new
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.
SELECT LEFT(EMP_PAY,CHARINDEX(‘,’,EMP_PAY)-1) FROM EMP_demo
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.
Right Click on the table and select Edit and insert a new column at your preferred order
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
It can be done using dynamic SQL
Refer this blog that explains how to split delimited data into seperate columns using a set-based approach
@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 :
~ 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
Hi Prasad
here is the solution
declare @t table (id int identity,Normal bit)
insert into @t values(1),(1),(0),(1),(0),(1),(1)
Select *,case when Normal=1 then 0
when Normal=0 then 1 end as Abnormal from @t
@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
Another method would be
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, Isnormal^1 Abnormal
FROM
Data;
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.
Hi Vineesh
Your solution is as follows
declare @t table (Company nvarchar(100),Area nvarchar(100), EmployeeNo int)
insert into @t values(‘Abc’,’US’,Null),(‘Abc’,’US’,Null),(‘Abc’,’US’,Null)
Select * from @t
———-=====================================================================
declare @Str nvarchar(100)=’1234,4567,7890′,@D char=’,’,@Pos1 int,@Pos2 int=1
set @Str=@Str+@D
Set @Pos1=charindex(@D,@Str,1)
declare @t1 table(id int identity,Number int)
while @Pos1>0
————-Spliting Numbers saperated by comma
begin
insert into @t1
Select Substring(@Str,@Pos2,@Pos1-@Pos2)
set @Pos2=@Pos1+1
set @Pos1=charindex(@D,@Str,@Pos1+1)
end
———-======================================================================
update A
set A.EmployeeNo=B.Number
from
(Select row_number() over(order by Company)RNK,Company,Area,EmployeeNo from @t)A
inner join @t1 B
on A.RNK=B.id
Select * from @t
Hello Sir
In Sql table i have a column name ‘Course’ in this column has many courses like B.teach,MCA,MBA etc seperated by commas..sir i have to read one value from the column which has multiple values..so plz give me guideline…
thank you for reply