Concatenating Fields

In SQL SELECT statements, you can concatenate columns using a special operator. Depending on which DBMS you are using, this can be a plus sign (+) or two pipes (||).

+ or ||? Access, SQL Server, and Sybase support + for concatenation. DB2, Oracle, PostgreSQL, and Sybase support ||. Refer to your DBMS documentation for more details.

|| is actually the preferred syntax, so more and more DBMSs are implementing support for it.

 

SELECT vend_name + ' (' + vend_country + ')'

FROM Vendors

ORDER BY vend_name;

Many databases (although not all) save text values padded to the column width. To return the data formatted properly, you must trim those padded spaces. This can be done using the SQL RTRIM() function

 

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'

FROM Vendors

ORDER BY vend_name;

The TRIM Functions Most DBMSs support RTRIM() (which, as just seen, trims the right side of a string), as well as LTRIM() (which trims the left side of a string), and TRIM() (which trims both the right and left).