String Concat in SQL?

[Mark D Stock]

Michael S. Verrilli wrote:
>
> Anyone know the syntax (if there is one) for concatting strings
> in SQL? I can do it in ACE but it would be a lot easier to
> update tables from SQL.

It's double pipe (||). But I haven't found a CLIPPED function yet! ;-)

Cheers,
--

Mark.

+-------------------------------------------------------------------------+
|Mark D. Stock - The West Solutions Group http://www.west.co.za |
| |
|mailto:ma...@west.co.za +------------------------------------------------+
|Tel: +27 11 803 2151 |If it doesn't work... force it! |
|Fax: +27 11 803 2189 |If it breaks... it needed replacing anyway! |
|Cell: +27 83 250 2325 |Well, that's how I code anyway! |
+------------------------+------------------------------------------------+

[John Gose]

Michael S. Verrilli wrote:
>
> Anyone know the syntax (if there is one) for concatting strings
> in SQL? I can do it in ACE but it would be a lot easier to
> update tables from SQL.
>

> Thanks in advance for the help.
>
> --
> Mike

Given two fixed length character fields f1 and f2 in table t1 the syntax
would be:
select f1 || f2 from t1;

If you wish to trim the strings then something like:

select trim(f1) || trim(f2) from t1;

All information found on pg 547 & 580 of the Informix Guide to SQL
Syntax, version 7.1. Won't say that it will work for any earlier
versions.

John Gose

[Jonathan Leffler]

>But I haven't found a CLIPPED function yet! ;-)

Probably because it is called TRIM().

Yours,
Jonathan Leffler (jo...@informix.com) #include <disclaimer.h>

[Sandy Spiers]

On Jul 18, 8:51pm, Michael S. Verrilli wrote:
> Subject: String Concat in SQL?

> Anyone know the syntax (if there is one) for concatting strings
> in SQL? I can do it in ACE but it would be a lot easier to
> update tables from SQL.
>
> Thanks in advance for the help.
>

>-- End of excerpt from Michael S. Verrilli

Michael,
I think your looking for the '||' string concatenation operator which you can
use to concatenate strings in sql, for example..

update <table_name> set <column_a> = "test "||"string";

or if you want the result of some columns, say to join the customers first
and last names to make up their full name you can do something like...

update customer set full_name = first_name||last_name;

Hope this helps but email me if you have any problems.

--

Cheers Phone: 0131-479-1271
sandys EMail: san...@easter.euro.csg.mot.com

"I have ruined Glencoe." Colonel Sir John Hill, Feb 14 1692.

[Dennis J Pimple]

Michael S. Verrilli wrote:
>
> Anyone know the syntax (if there is one) for concatting strings
> in SQL? I can do it in ACE but it would be a lot easier to
> update tables from SQL.
>
> Thanks in advance for the help.

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.

>
> --
> Mike

--
//////////////// =======================================================
////////// // Dennis J. Pimple Informix Software, Inc.
////// / /// Principal Consultant 5299 DTC Blvd Suite 740
///// // //// den...@informix.com Englewood CO 80111
//// // /////
/// // ////// recept: 303-850-0210
// // /////// direct: 303-740-5611 Opinions expressed are mine,
/ /////////// fax: 303-843-6408 and do not necessarily
//////////////// http://www.informix.com reflect those of my employer

[Koen Van der Elst]

>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