[dba-SQLServer] concatenating strings where one or more string is null

c) Use the ISNULL() function


ISNULL(t.StreetNrPOBox + ', . . . ','') + ISNULL(t.StreetName + ', . . . ','') + 
ISNULL(t.Suburb + ', .. .','') as xyz

Note that I've included  the  field and string in the ISNULL() so that if the 
field is null, you don't get two sets of ', . . . .' 


On 8 Jul 2008 at 10:03, Borge Hansen wrote:

> Hi,
> 
> A very simple one:
> 
> What is 'best practise way' of handling something like the following in a
> select sp or view:
> 
> 
> t.StreetNrPOBox + ', . . . ' + t.StreetName + ', . . . ' + t.Suburb + ', . .
> .' as xyz
> 
> where one of the column values may be null ??
> 
> MS Help in BOL is again not very helpful ... (topic: concatenation [SQL
> Server] )
> 
> They talk about
> 
> ..Just like arithmetic operations that are performed on null values, when a
> null value is added to a known value the result is typically an unknown
> value, a string concatenation operation that is performed with a null value
> should also produce a null result. However, you can change this behavior by
> changing the setting of CONCAT_NULL_YIELDS_NULL for the current session. For
> more information, see SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).
> 
> Then for SET CONCAT_NULL_YIELDS_NULL { ON|OFF } the first thing you read is:
> 
>   Important:  This feature will be removed in a future version of Microsoft
> SQL Server. Avoid using this feature in new development work, and plan to
> modify applications that currently use this feature.
> 
> So, what's best practise here:
> 
> a) Don't allow null in any text fields in your table definitions?
> 
> b) use a case statement to test for null
> 
> c) ?
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>