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
Contents
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 |
---|---|
|
|
|
|
|
|
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.
- Execute the following
SELECT
statement to concatenate the result of the query with the textis 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:
- 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));