Concaternation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concaternation problem

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2008-08-22 : 05:27:54

I want to insert a concaternated value from #table2 with the delimiter '/' between the different strings,
if a value is missing the delimiter should not be visible.

create table #table1
(
id int identity(1,1),
string varchar(100) null
)

create table #table2
(
id int not null,
string1 varchar(100) null,
string2 varchar(100) null,
string3 varchar(100) null,
string4 varchar(100) null
)

insert into #table2(id, string1, string2, string3, string4)
values(1, 'test1', 'test2', '', 'test4')
insert into #table2(id, string1, string2, string3, string4)
values(2, 'test1', ' ', '', 'test4')
insert into #table2(id, string1, string2, string3, string4)
values(3, ' ', 'test2', '', ' ')
insert into #table2(id, string1, string2, string3, string4)
values(4, 'test1', 'test2', 'test3', 'test4')
insert into #table2(id, string1, string2, string3, string4)
values(5, 'test1', 'test2', '', 'test4')

Expected output in #table1:
id string
1 test1/test2/test3
2 test1/test4
3 test2
4 test1/test2/test3/test4
5 test1/test2/test4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-22 : 05:39:53

[code]SELECT left(string, LEN(string) - 1)
FROM
(
SELECT coalesce(NULLIF(string1, '') + '/', '') +
coalesce(NULLIF(string2, '') + '/', '') +
coalesce(NULLIF(string3, '') + '/', '') +
coalesce(NULLIF(string4, '') + '/', '') AS string
FROM #table2
) t[/code]

KH
[spoiler]Time is always against us[/spoiler]

maevr
Posting Yak Master

169 Posts

Posted - 2008-08-22 : 05:43:47

Thanks for you help khtan!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-22 : 05:46:38

You are welcome

KH
[spoiler]Time is always against us[/spoiler]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-08-22 : 05:53:31

quote:

Originally posted by khtan

SELECT	left(string, LEN(string) - 1)
FROM
(
	SELECT	coalesce(NULLIF(string1, '') + '/', '') + 
		coalesce(NULLIF(string2, '') + '/', '') + 
		coalesce(NULLIF(string3, '') + '/', '') + 
		coalesce(NULLIF(string4, '') + '/', '') AS string
	FROM	#table2
) t

KH
[spoiler]Time is always against us[/spoiler]

Beware that this will error if all the values are blank. If you need to handle that, you can just use a case statement...


SELECT	case when string = '' then '' else left(string, LEN(string) - 1) end
    ...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-22 : 05:59:10

Good catch

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources