Concatenating Fields

To demonstrate working with calculated fields, let's start with a simple example—the mailing address.

The Vendors table contains vendor name and address information. Imagine that you are sending out a mailing to all your vendors and that you are printing labels using a report you have created. The label report requires three lines of text—the vendor name and two address lines.

The SELECT statement that returns all the vendor names is simple enough, but how would you create the second address line? The label application wants a single value, and the data in the table is stored in three columns: vend_city, vend_state, and vend_zip.

The solution is to concatenate the three columns. In SQL SELECT statements, you can concatenate columns using a special operator. Depending on what DBMS you are using, this can be a plus sign (+) or two pipes (||). Here's an example (using the plus sign, the syntax used by Microsoft SQL Server, Microsoft Access, and Sybase):

SELECT vend_city+', '+vend_state+' '+vend_zip
FROM Vendors
ORDER BY vend_name;

---------------------------------------------------------------
Anytown          ,             OH           44333
Bear Town        ,             MI           44444
Dollsville       ,             CA           99999
New York         ,             NY           11111

The following is the Oracle version of the same statement:

SELECT vend_city || ', ' || vend_state || ' ' || vend_zip
FROM Vendors
ORDER BY vend_name;

---------------------------------------------------------------
Anytown          ,             OH           44333
Bear Town        ,             MI           44444
Dollsville       ,             CA           99999
New York         ,             NY           11111

The above SELECT statements concatenate the following elements:

• The city stored in the vend_city column.

• A string containing a comma and a space that will go between the City and State columns.

• The state stored in the vend_state column.

• A string containing a space that will go between the State and Zip columns.

• The Zip code stored in the vend_zip column.

As you can see in the output shown above, the SELECT statement returns a single column (a calculated field) containing all these five elements as one unit.

Look again at the output returned by the SELECT statement. The three columns that are incorporated into the calculated field are padded with spaces. 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, as follows:

SELECT RTRIM(vend_city)+', '+RTRIM(vend_state)+' '+RTRIM(vend_zip)
FROM Vendors
ORDER BY vend_name;

---------------------------------------------------------------
Anytown, OH 44333
Bear Town, MI 44444
Dollsville, CA 99999
New York, NY 11111

Here's the Oracle version:

SELECT RTRIM(vend_city) || ', ' || RTRIM(vend_state) || ' ' || RTRIM(vend_zip)
FROM Vendors
ORDER BY vend_name;

---------------------------------------------------------------
Anytown, OH 44333
Bear Town, MI 44444
Dollsville, CA 99999
New York, NY 11111

The RTRIM() function trims all space from the right of a value. By using RTRIM(), the individual columns are all trimmed properly. A comma and space separate the city and state, and a space separates the State and Zip.

Using Aliases

The SELECT statement used to concatenate the address field works well, as seen in the above output. But what is the name of this new calculated column? Well, the truth is, it has no name; it is simply a value. Although this can be fine if you are just looking at the results in a SQL query tool (for instance, MS-Query, or Microsoft SQL Server Query Analyzer), an unnamed column cannot be used within a client application because there is no way for the client to refer to that column.

To solve this problem, SQL supports column aliases. An alias is just that, an alternate name for a field or value. Aliases are assigned with the AS keyword. Take a look at the following SELECT statement:

SELECT RTRIM(vend_city)+', '+RTRIM(vend_state)+' '+RTRIM(vend_zip) AS address2
FROM Vendors
ORDER BY vend_name;

address2
---------------------------------------------------------------
Anytown, OH 44333
Bear Town, MI 44444
Dollsville, CA 99999
New York, NY 11111

Here's the Oracle version:

SELECT RTRIM(vend_city) || ', ' || RTRIM(vend_state) || ' ' || RTRIM(vend_zip) AS address2
FROM Vendors
ORDER BY vend_name;

address2
---------------------------------------------------------------
Anytown, OH 44333
Bear Town, MI 44444
Dollsville, CA 99999
New York, NY 11111

The SELECT statement itself is the same as the one used in the previous code snippet, except that here the calculated field is followed by the text AS address2. This instructs SQL to create a calculated field named address2 containing the calculation specified. As you can see in the output, the results are the same as before, but the column is now named address2, and any client application can refer to this column by name, just as it would to any actual table column.

The complete SELECT statement to retrieve all the information for the mailing label now looks like this:

SELECT vend_name,
        vend_address,
        RTRIM(vend_city)+', '+RTRIM(vend_state)+' '+RTRIM(vend_zip) AS vend_address2
FROM Vendors
ORDER BY vend_name;

Here's the Oracle version:

SELECT vend_name,
        vend_address,
        RTRIM(vend_city) || ', ' || RTRIM(vend_state) || ' ' || RTRIM(vend_zip) AS
 vend_address2
FROM Vendors
ORDER BY vend_name;