community.borland.com

Article #25523: How to Concatenate Strings in InterBase

Problem:
How can I add two strings together in InterBase.  When I try to do it, using a "+"
symbol,  I get a type conversion error.


Solution:

1.  In a stored procedure or trigger:
   You can set the value of a variable in a stored procedure or 
    trigger to be the concatenation of  two strings using 2 pipe symbols
    eg.  full_name = first_name || ' ' || last_name;
    This would return values like "Sally Smith"

2.  In a select statement: 
In a select statement, it works in much the same way.
For the example below, the select is being done against 
a table called buildings that has one record in it.
The table looks like this:

Building                 Building_NUMBER
-----------                  ---------------------------
apple                       12

eg.  To get a concatenation of strings in the result of a select statement, 
you would use the following syntax:

select BuildingName = 'Building' || BuildingNumber 
would result  in the string "Building12" being returned.

If you use the syntax  :
select  Building || Building_Number from buildings

you will get this result:         
apple12           

***Notice that when you put quotation marks around a value, it becomes a literal.
This may come in handy with names, eg. 

select 'Mr.' ||' ' ||  first_name || ' ' || last_name from football_players
to get the first and last name of a football player with Mr. in front of it.

The query above would return values like:
       Mr.  Reggie White

Last Modified: 26-OCT-00