Simplify String Concatenations with Nulls and Zero
Length Strings
Provided by: Luke Chung, FMS President
The concatenation of string values can be a nuisance when
referencing fields with Null values, because you want to avoid having extra
spaces where the missing string value should have appeared.
A classic example of this is a person's name and their
spouse. You want to add the " and Spouse" to the name if the Spouse field is
filled.
Here's the data:
|
PersonID |
FirstName |
LastName |
Spouse |
| 1 |
Robert |
Jones |
|
|
2 |
Thomas |
Smith |
Jane |
| 3 |
Jack |
Thompson |
Sally |
| 4 |
William |
Johnson |
|
And the results we want:
| PersonID |
Person |
| 1 |
Robert Jones |
| 2 |
Thomas and Jane Smith |
| 3 |
Jack and Sally Thompson |
| 4 |
William Johnson |
Zero Length Strings Allowed
If the Spouse field allows zero length string values ("") --
its AllowZeroLength property is True, you need to consider both Nulls and
"". You should use the NullToZero function NZ, to handle either case:
SELECT PersonID, FirstName & IIF(Nz(Spouse)<>"", " & " &
Spouse) & " " & LastName AS Person
FROM tblPerson
The NZ function evaluates the value it is passed. For text
fields, it returns "" if it's null or "". For numeric and dates fields, it
returns zero if it's null. This makes it easy to handle nulls in either
case.
Zero Length Strings Not Allowed
If the Spouse field does not allow zero length strings,
blank values are always null. In this situation, we can avoid using a
user-defined/built-in function to test for null. The concatenation of a
string value and a null value will return a null value. Thus, you eliminate
the extra processing needed:
SELECT PersonID, FirstName & (" and " + Spouse) & " " &
LastName AS Person
FROM tblPerson
By using the "+" command to combine the " and " and [Spouse]
fields, the result is nothing if the Spouse field is null. This is a
difference from using the "&" command which concatenates fields and would
leave the " and " if Spouse is null.
Return to the tips page
|
