March 13, 2009 at 1:34 pm
I am working on SQL 2005
I need to store values from different fields in a new field. No need to take the column if no value. Something like this:
col1 col2 col3 col4 col5
test ----- ----- later Col1: Test Col4:later
I used update statement with case statement for col5
update t
set col5=case when col1='' then'' else 'col1:' + col1 end +char (13)+
case when col2='' then '' else 'col2:'+col2 end +char (13)+
case.....
Result:
col1: Test
--
--
Col4: Later
Its seems like it is leaving 2 spaces for col2 and col3
How do I remove these space?
expected result is:
col1: Test
col4: Later
I tried Replace() but it is not working
update t
set col5=replace (' ','')
Thanks
March 13, 2009 at 1:40 pm
I find the easiest way to do this in SQL is nesting NullIf and IsNull statements.
update dbo.MyTable
set Col5 =
isnull(nullif(Col1, ''), '') +
isnull(nullif(Col2, ''), '') +
isnull(nullif(Col3, ''), '') +
isnull(nullif(Col4, ''), '');
You can do more stuff with it if you want something in between the columns, just add it inside the IsNull, like "isnull(nullif(Col1, '') + ':', '')". That way, if Col1 is just an empty space, it will ignore it, but if it isn't, it will add a colon after it.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 13, 2009 at 2:25 pm
I used your script for the table below:
create table test
(col1 varchar(30),
col2 varchar(30),
col3 varchar(30))
insert into test (col1, col2, col3)
values('hi there', '111', '')
insert into test (col1, col2, col3)
values('', '', 'Hello')
insert into test (col1, col2, col3)
values('', 'This is a Test', '')
alter table test
add col5 varchar(30)
update dbo.test
set col5=
isnull(nullif('col1:'+Col1, ''), '') +
isnull(nullif('col2:'+Col2, ''), '') +
isnull(nullif('col3:'+Col3, ''), '');
I am getting this result:
col1:hi therecol2:111col3:
col1:col2:col3:Hello
col1:col2:This is a Testcol3:
1. From UI, the contents of each col should be displayed in next line. Can we give a line break here?
e.g:
hi there
111
2. Also if there is no value then no need to to track the field. This is the expected result:
col1:hi there col2:111
col3:Hello
col2:This is a Test
Thanks
March 13, 2009 at 9:23 pm
Hi,
instead of giving the '' you give null
create table #test
(col1 varchar(30),
col2 varchar(30),
col3 varchar(30))
--
insert into #test (col1, col2, col3)
values('hi there', '111', null)
--
insert into #test (col1, col2, col3)
values(null, null, 'Hello')
--
insert into #test (col1, col2, col3)
values(null, 'This is a Test', null)
alter table #test
add col5 varchar(30)
--
update #test
set col5=
isnull(nullif('col1:'+Col1, ''), '') +
isnull(nullif('col2:'+Col2, ''), '') +
isnull(nullif('col3:'+Col3, ''), '')
--
result:
col1:hi therecol2:111
col3:Hello
col2:This is a Test
ARUN SAS
March 16, 2009 at 8:57 am
The reason you're getting the column names where you don't want them is you have to put those outside the NullIf, or you need to modify the NullIf comparison.
Try this version, see if it does what you need. The "char(10)+char(13)" should give you a line-return, as requested. If you decide you don't want that in the query, take that part out.
update dbo.test
set col5=
isnull('col1:'+nullif(Col1, '')+char(10)+char(13), '') +
isnull('col2:'+nullif(Col2, '')+char(10)+char(13), '') +
isnull('col3:'+nullif(Col3, '')+char(10)+char(13), '');
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2009 at 11:58 am
Perfect!! It worked. Thanks o much, GSquared.
March 16, 2009 at 12:00 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply