Re: String Concat in SQL?
>SELECT columna || columnb
>would concatenate columna and columnb, including any trailing blank
>spaces in columna. In more recent versions (7.1?) of the database:
>
>SELECT trim(columna) || " " || trim(columnb)
>would concatenate the columns and put one space between them.
>
>Try any SELECT with trim(); if you get a syntax error pointing to trim,
>your version of the database doesn't have the feature.
Waytch out : if one of the columns you're contatenating contains a
NULL value, the operation fails !
Solution : create a SPL functions which I call nvl (sorry for the
Oracle terminology)
create procedure nvl(stringvalue char(255)) returning char (255);
if stringvalue is NULL
return ""
else
return stringvalue
end procedure;
Then you can write trim(nvl(columna)) || trim(nvl(columnb)).
Credit for this one goes to Gerrit Verlinden, Support Engineer of
Informix Belgium. He came up with the idea in 4GL, I just adapted it
to SPL.
Koen