|
Category: SQL Server
view more software Tips and Tricks
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 |