InterBase Quick Start: Part IV - Formatting Data

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

This section describes three ways to change data formats:

• Using CAST to convert data types
• Using the string operator to concatenate strings
• You can convert characters to uppercase

Using CAST to Convert Data Types

Normally, only similar data types can be compared in search conditions, but you can work around this by using CAST. Use the CAST clause in search conditions to translate one data type into another. 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 another DATE column, 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

Compatible Data Types for CAST

Using the String Operator in Search

The string operator, also referred to as a concatenation operator, ||, joins two or more character strings into a single string. The strings to be joined can be the result set of a query or can be quoted strings that you supply. The operator is the pipe character, typed twice.

Using the String Operator to Join Strings

1. Execute the following SELECT statement to concatenate the result of the query with the additional text “is the manager”. Remember to have a space as the first character of the string. The query returns the manager names for all department 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

You should see the following result:

2. You can concatenate as many strings as you like. The following query is a slight variation on the previous one: it concatenates the first name to the other output strings:

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

Notice that in order to get a space between the first and last names, you have to concatenate a string that consists solely of a space. The result set should look like this:

Converting to Uppercase

The UPPER function converts character values to uppercase. For example, you could include a CHECK constraint that ensures that all column values are entered in uppercase when defining a table column or domain. The following CREATE DOMAIN statement uses the UPPER function to guarantee that column entries are all upper case:

CREATE DOMAIN PROJNO 
 AS CHAR(5) 
 CHECK (VALUE = UPPER (VALUE));

Advance To:

• Part V - Advanced Topics