View Full Version : How do you concatinate when one value is Null?
| I am trying to combine 2 fields in a SQL statement as follows: Select Field1 + Field2 AS tablefields from table However when Field2 is Null it makes the column tablefields null regardless of whether Field1 has data or not. Is there a way to avoid this? Note* The real SQL statement is a series of LEFT OUTER JOINS combining fields from different tables into one field, but if one of the fields is Null it makes the entire end result for that column Null. Travis |
| In this instance I am using SQL Server 2000 which doesn't have DECODE. I figured it out though, have to use the CASE Function like such: Select (CASE WHEN Field1 IS NULL THEN '' ELSE Field1) + (CASE WHEN Field2 IS NULL THEN '' ELSE Field2 END) As bothfields From Table Thanks for the lead. Am I posting this on the write Forum? Is this only for Oracle/PSQL? Travis Originally posted by alligatorsql.com Hello Travis, every operation with NULL comes to NULL a + NULL = NULL a || NULL = NULL and so on and so on What you are seeking for is DECODE. Try your statement with the following commands Select Field1 || DECODE(Field2, NULL, '', Field2) AS tablefields from table I hope that you are using Oracle otherwise found the counterpart to DECODE ... Hope this helps ? Regards Manfred Peter (Alligator Company) http://www.alligatorsql.com |