Using the String Operator in Search

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

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:

Advance To:

• Converting to Uppercase