Home / Software / Tips and Tricks / Format an address in Tsql Using Coalesce?

Format an address in Tsql Using Coalesce?

Updated:  06/23/2014 08:06 AM
Author:  Shiju Mathews

Status:    Resolved.


How to format an address line by eliminating the unwanted spaces and columns in SQL Server?

Coalesce evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

SELECT REPLACE([PrimaryNumber]
+ ' ' + Coalesce([StreetPreDirection],'*')
+ ' ' + Coalesce([StreetName],'*')
+ ' ' + Coalesce([StreetSuffix],'*')
+ ' ' + Coalesce([StreetPostDirection],'*')
+ ' ' + Coalesce([SecondaryDesignator],'*')
+ ' ' + Coalesce([SecondaryNumber],'*')
+ ' ' + Coalesce([ExtraSecondaryDesignator],'*'
+ ' ' + Coalesce([ExtraSecondaryNumber],'*')
),' *','')
from dbo.Address