address formatting using TSQL - 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 2000 Forums

Transact-SQL (2000)

address formatting using TSQL

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-05-09 : 09:23:52

hi,
how can I write :
[Street] & IIf(IsNull([Locality]), "", IIf(IsNull([Street]), "", [, ]) & [Locality]) & IIf(IsNull([Street]) AND IsNull([Locality]), "", [, ]) & [Town] AS Address,
in TSQL.

so addresses are formatted correctly....

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-05-09 : 09:50:29

IFF is an ACCESS construct
IT's T-SQL replacement is CASE

Search here/BOl for examples.

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-05-09 : 09:57:38

so can I have cases within cases ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-05-10 : 04:53:21

cool,
its actually very easy isnt it ....

SELECT CASE WHEN [Street] is null THEN '' ELSE Street + ', ' END
+ CASE WHEN [Locality] is null THEN '' ELSE Locality + ', ' END
+ CASE WHEN [Town] is null THEN '' ELSE Town END AS Address
FROM TABLE1

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-05-10 : 05:41:05

There's a shorthand way of getting the same result using coalesce:



SELECT
	COALESCE([Street] + ', ', '')
	+ COALESCE([Locality] + ', ', '')
	+ COALESCE([Town] + ', ', '') AS Address
FROM 
	TABLE1

N.B. This relies on CONCAT_NULL_YIELDS_NULL being set to true (which is the default).
Also, if you have any empty strings in your address fields and you don't want a trailing comma for those elements, you can wrap each one in a NULLIF i.e.:


COALESCE(NULLIF([Street], '') + ', ', '')

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources