Help with Case logic..... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Case logic.....

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-03 : 10:37:43

I would like to display our address information as one 'whole_address' string. In the case where any address field is empty or null, do not include it in the 'whole_address' string.

select rtrim(add1) + rtrim(add2) + rtrim(city) + rtrim(state) + rtrim(zip) as 'whole_address'
from address

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 10:41:14

[code]select rtrim(coalesce(add1,'')) + rtrim(coalesce(add2,'')) + rtrim(coalesce(city,'')) + rtrim(coalesce(state,'')) + rtrim(coalesce(zip,'')) as 'whole_address'
from address[/code]

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-03 : 10:48:42

Sweet, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 11:02:02

Cheers

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-03 : 11:32:54

So now I am stringing together my address data into one field (also including a little minor formatting), no problem.....

rtrim(coalesce(Addr.Line1,'')) + ', ' + rtrim(coalesce(Addr.Line2,'')) + ', ' + rtrim(coalesce(Addr.City,'')) + ', ' + rtrim(coalesce(Addr.State,'')) + ', ' + rtrim(coalesce(Addr.Zip,'')) + ', ' + rtrim(coalesce(Addr.Country,''))

The above SQL inserts a comma and space regardless if there is a value present.
Any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 11:35:02

sure..modify as below

rtrim(coalesce(Addr.Line1+ ', ','')) + rtrim(coalesce(Addr.Line2+ ', ','')) + rtrim(coalesce(Addr.City+ ', ','')) + rtrim(coalesce(Addr.State+ ', ','')) + rtrim(coalesce(Addr.Zip+ ', ','')) + rtrim(coalesce(Addr.Country,''))

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-03 : 12:47:55

Thanks Visakh16, when I implement your edit, my results change as shown below....
The right Trim is being ignored, and the space after the comma is being eliminaged. Do you think the coalesce need to be before the rtrim?

CLIENT SERVICES ,700 SOUTH STREET ,PITTSFIELD ,MA,01201 ,USA
100 MADISON STREET, MD 34-3 , ,SYRACUSE ,NY,13202 ,USA
720 E. WISCONSIN AVE. E03-K , ,MILWAUKEE ,WI,53202-4797,USA
H-GS-3 ,ONE AMERICAN ROW ,HARTFORD ,CT,06102-5056,USA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 12:55:18

then put rtrim inside coalesce.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-03 : 13:00:52

The trim has been fixed by moving the coalesce to come before the rtrim.

coalesce(rtrim(Addr.Line1) + ', ','') + coalesce(rtrim(Addr.Line2) + ', ','') + coalesce(rtrim(Addr.City) + ', ','') + coalesce(rtrim(Addr.State) + ', ','') + coalesce(rtrim(Addr.Zip) + ', ','') + coalesce(rtrim(Addr.Country),'')

The problem now is the stripping of Address 2 when empty, null, or '' (see below)

1295 STATE ST., , SPRINGFIELD, MA, 01111-0001, USA
720 E. WISCONSIN AVE. E03-K, , MILWAUKEE, WI, 53202-4797, USA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 13:06:13

what does addr2 contain for those rows?

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-03 : 13:18:25

Address2 (Line2) is empty for the above two examples, it does not contain anything.

Example 1
Add1 = 1295 STATE ST.
Add2 =

Example 2
Add1 = 720 E. WISCONSIN AVE. E03-K
Add2 =

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources