InterBase Quick Start: Part IV - Formatting Data

Go Up to InterBase Quick Start: Part IV - Retrieving Data

There are three ways you can change the format of the data:

• Using CAST to convert data types.
• Using the string operator (|| ) to concatenate strings.
• Using UPPER to convert characters to uppercase

Using CAST to Convert Data Types

You can only comapre similar data types in a search condition, but you can work around this limitation by using CAST. The syntax for the CAST clause is:

CAST ({<value> | NULL} AS data_type)

For example, the following WHERE clause uses CAST to translate a CHAR data type (interview_date) to a DATE data type. This conversion lets you compare interview_date to hire_date:

WHERE hire_date = cast(interview_date AS DATE)

You can use CAST to compare columns in the same table or across tables. CAST allows the conversions listed in the following table:

From data type	To data type
NUMERIC	CHARACTER, DATE
CHARACTER	NUMERIC, DATE
DATE	CHARACTER, NUMERIC

Using the String Operator to Join Strings

The string operator, also known as a concatenation operator (|| ), joins two or more character strings into a single string. The source strings can be a result of a query or can be strings that you specify.

1. Execute the following SELECT statement to concatenate the result of the query with the text is the manager. The query returns the names of managers for all departments that are not field offices.

   SELECT   d.dept_no,
            d.department,
            e.last_name
                     || ' is the manager'
   FROM     department d,
            employee e
   WHERE    d.mngr_no = e.emp_no
   AND      d.department NOT containing 'Field'
   ORDER BY d.dept_no
   

   The image below shows the expected result:

   

2. You can concatenate as many strings as you like. The following query is a variation on the previous query: it adds the first name of a manager to the previous output:

   SELECT   d.dept_no,
            d.department,
            e.first_name
                     || ' '
                     || e.last_name
                     || ' is the manager'
   FROM     department d,
            employee e
   WHERE    d.mngr_no = e.emp_no
   AND      d.department NOT containing 'Field'
   ORDER BY d.dept_no
   

   In order to add a space between the first and last name, you have to insert a string that consists of a space. The image below shows the expected result:

   

Converting to Uppercase

The UPPER function converts characters to uppercase. For example, you can add a CHECK constraint that ensures all column values are in uppercase when defining a table column or domain. The following CREATE DOMAIN statement uses the UPPER function to ensure column entries are all uppercase:

CREATE domain projno AS CHAR(5) CHECK (value = upper (value));

Advance To:

• Part V - Advanced Topics