String Operations in Oracle

In order to compare an attribute with a string, it is required to surround the string by apostrophes,
e.g., where LOCATION = ’DALLAS’. A powerful operator for pattern matching is the
like operator. Together with this operator, two special characters are used: the percent sign
% (also called wild card), and the underline , also called position marker.

For example, if one is interested in all tuples of the table DEPT that contain two C in the name of the department, the condition would be where DNAME like ’%C%C%’. The percent sign means that any
(sub)string is allowed there, even the empty string. In contrast, the underline stands for exactly
one character. Thus the condition where DNAME like ’%C C%’ would require that exactly one
character appears between the two Cs. To test for inequality, the not clause is used.

Further string operations are:

• upper() takes a string and converts any letters in it to uppercase, e.g., DNAME
= upper(DNAME) (The name of a department must consist only of upper case letters.)

• lower() converts any letter to lowercase,

• initcap() converts the initial letter of every word in to uppercase.

• length() returns the length of the string.

• substr(, n [, m]) clips out a m character piece of , starting at position
n. If m is not specified, the end of the string is assumed. substr(’DATABASE SYSTEMS’, 10, 7)
returns the string ’SYSTEMS’.