concatenation - 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

concatenation

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 00:32:54

Hi. I want to make a query that concatenate the address of the person with a comma inbetween and neglecting the null value.

table - pspersonaldata
dcno name streetbldg1 streetbldg2 streetbldg3
1 jon santol1 null santol3
2 mike mangga1 mangga2 null
3 jay langka1 langka2 langka3
4 joy null buko2 buko3
5 jean null null null

expected output is:

name address
jon santol1, santol3
mike mangga1, mangga2
jay langka1, langka2, langka3
joy buko2, buko3
jean null

thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 00:44:23

[code]
select name,
address = isnull(streetbldg1, '') + isnull(', ' + streetbldg2, '') + isnull(', ' + streetbldg3, '')
from pspersonaldata
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-07 : 01:05:20

Buko doesn't handle very well.

select name,
case when address = ', , ' then null
when address like ', , %' then substring(address, 5, 8000)
when address like ', %' then substring(address, 3, 8000)
else address
end
from (
select name,
address = isnull(streetbldg1, '') + isnull(', ' + streetbldg2, '') + isnull(', ' + streetbldg3, '')
from pspersonaldata
) as x

Peter Larsson
Helsingborg, Sweden

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 01:09:20

the result for this is:
name address
jon santol1, santol3 - OK
mike mangga1, mangga2 - OK
jay langka1, langka2, langka3 - OK
joy ,buko2, buko3 - NOT OK. It has comma before
jean null - OK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-07 : 01:17:27

select name,
case when address = ', , ' then null
when address like ', , %' then substring(address, 5, 8000)
when address like ', %' then substring(address, 3, 8000)
else replace(address, ', , ', ', ')
end
from (
select name,
address = isnull(streetbldg1, '') + isnull(', ' + streetbldg2, '') + isnull(', ' + streetbldg3, '')
from pspersonaldata
) as x

Peter Larsson
Helsingborg, Sweden

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 01:27:28

Thank peso, KH.
Your query works very well. But, it will not work if the field has no value at all. The field has no NULL value specified. Just an empty field.

Thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 01:31:43

empty field as in '' ?
use nullif(), example

nullif(streetbldg1, '')

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 01:35:33

yes, as in ''.

use nullif(streetbldg1, '') where can i insert this in my query?
thanks
-Ron-

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 01:39:47

i changed isnull to nullif... takes no effect.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 01:51:07

no no no. the isnull still there. add a nullif to check for empty string


address = isnull(nullif(streetbldg1, ''), '') + 
          isnull(', ' + nullif(streetbldg2, ''), '') + 
          isnull(', ' + nullif(streetbldg3, ''), '')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 02:04:59

extracted from BOL

quote:

ISNULL
Replaces NULL with the specified replacement value.

Syntax
ISNULL ( check_expression , replacement_value )

quote:

NULLIF
Returns a null value if the two specified expressions are equivalent.

Syntax
NULLIF ( expression , expression )

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 02:18:38

Thank u Thank u Thank u.
Fantastic.

1 more thing. The column name of the address is (No column Name). How could i change that?

-Thanks-
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 02:19:58

use alias


 select name,
case when address = ', , ' then null
when address like ', , %' then substring(address, 5, 8000)
when address like ', %' then substring(address, 3, 8000)
else replace(address, ', , ', ', ')
end as address
...

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-08 : 21:24:04

Hi all. How can I concatenate integer variables? I think i have to convert them first to string, but dont know how to do it.

select [SY] = year1 + '-' + year2 from hrappempeducs

it returns : 4005
my expected result is: 2000 - 2005

Thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 21:25:26

convert to string. use cast() or convert()


select [SY] = convert(varchar(10), year1) + '-' + convert(varchar(10), year2) from hrappempeducs

select [SY] = cast(year1 as varchar(10)) + '-' + cast(year2 as varchar(10)) from hrappempeducs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources