String Operations in SQL

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(<string>) 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(<string>) converts any letter to lowercase,
• initcap(<string>) converts the initial letter of every word in <string> to uppercase.
• length(<string>) returns the length of the string.
• substr(<string>, n [, m]) clips out a m character piece of <string>, 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’.