InterBase Quick Start: Part IV - Formatting Data

From InterBase
Jump to: navigation, search

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

Image 025.jpgUsing 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:

    TutorialFormatting1.png
  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:

    TutorialFormatting2.png

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: