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

On Sun, 12 Mar 2000 00:00:07 +0800, "Lawrence Choy" <choyls@pd.jaring.my> wrote: >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? select * from employee where firstname is null or middlename is null orlastname is null; Axel