RE: Concatenation in SQL

Ok, It's not pretty, but is should work: select fname || mname || lname from tab_name where fname is not null and mnane is not null and lname is not null union select fname || " " || lname from tab_name where fname is not null and mnane is null and lname is not null union select " " || mname || lname from tab_name where fname is null and mnane is not null and lname is not null -----Original Message----- From: Lawrence Choy [mailto:choyls@pd.jaring.my] Sent: Saturday, March 11, 2000 11:00 AM To: informix-list@iiug.org Subject: Concatenation in SQL Hi gurus, I have been trying to concatenate 3 character-datatype columns together using '||' operator in a simple SQL statement as this: SELECT firstname || middlename || lastname FROM employee However, I found out that if either one of the column is a NULL or empty value, the result returned is nothing at all. Can you gurus help me to solve this puzzle 'coz I am really running out of ideas. Don't ask me to RTFM, I have read almost every section on the SQL. :( Or, is it possible to detect if let's say middlename column is NULL, I replaced it with another value. May I know how to do that too? Please, please help me... Cheers, Lawrence Choy