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
- 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: