STRINGS, NULLS, AND CONCATENATION IN V6.5 AND v7.0
The ANSI SQL-92 standard, which SQL Server v7.0 meets, means that v6.5 
and v7.0 handle string concatenation differently. If you are v6.5 DBA 
beginning to use v7.0, know that string concatenation in v7.0 can be 
affected by NULL values.

We will demonstrate this discrepancy between v6.5 and v7.0 using the 
following dataset.

Fname    MI      Lname 
------   ----    ---------- 
Barney   NULL    Rubble 
Fred     E       Flintstone

If you were to issue the following statement to format the names of the 
dataset in both SQL Server v6.5 and v7.0, each would return different 
results.

SELECT Fname + ' ' + MI + ', ' + Lname from TableX

Version 6.5 would return

Barney  , Rubble 
Fred   E, Flintstone

Version 7.0, however, will return a NULL for the first entry.

NULL 
Fred   E, Flintstone

The NULL being returned in place of the Barney Rubble name is compliant 
with the ANSI SQL-92 standard; a string concatenated to a null yields a 
null string.

You can use the v7.0 ISNULL function to test for nulls and replace them 
with constant values. This would return a result similar to the one 
delivered in the 6.5 version.

SELECT Fname + ' ' + ISNULL(MI,' ') + ', ' + Lname from TableX

This would replace the NULL found in Barney Rubble's middle initial 
with a space.

Users new to v7.0 should be aware of these differences when 
concatenation of two or more strings is necessary.

------------------------------------------