Re: Concatenation in SQL

Lawrence If you have 7.31 you can use nvl() to replace the null middle name with some other character, say space. Frex: SELECT firstname || nvl(middlename,"") || lastname FROM employee HTH Sujit Lawrence Choy <choyls@pd.jaring.my> on 03/11/2000 08:00:07 AM Please respond to Lawrence Choy <choyL@logica.com> To: informix-list@iiug.org cc: 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