Additional Basic Operations-String Operations

SQL specifies strings by enclosing them in single quotes, for example, ’Computer’.
A single quote character that is part of a string can be specified by using two single
quote characters; for example, the string “It’s right” can be specified by “It”s right”.

The SQL standard specifies that the equality operation on strings is case sen?sitive; as a result the expression “’comp. sci.’ = ’Comp. Sci.’” evaluates to false.
However, some database systems, such as MySQL and SQL Server, do not distin?guish uppercase from lowercase when matching strings; as a result “’comp. sci.’ = ’Comp. Sci.’” would evaluate to true on these databases. This default behavior
can, however, be changed, either at the database level or at the level of specific
attributes.

SQL also permits a variety of functions on character strings, such as concate?nating (using “”), extracting substrings, finding the length of strings, converting strings to uppercase (using the function upper(s) where s is a string) and low?ercase (using the function lower(s)), removing spaces at the end of the string (using trim(s)) and so on. There are variations on the exact set of string functions supported by different database systems. See your database system’s manual for more details on exactly what string functions it supports.

Pattern matching can be performed on strings, using the operator like. We
describe patterns by using two special characters:

• Percent (%): The % character matches any substring.

• Underscore ( ): The character matches any character.

Patterns are case sensitive; that is, uppercase characters do not match lowercase
characters, or vice versa. To illustrate pattern matching, we consider the following
examples:

• ’Intro%’ matches any string beginning with “Intro”.

• ’%Comp%’ matches any string containing “Comp” as a substring, for exam?ple, ’Intro. to Computer Science’, and ’Computational Biology’.

• ’ ’ matches any string of exactly three characters.

• ’ %’ matches any string of at least three characters.

SQL expresses patterns by using the like comparison operator. Consider the query
“Find the names of all departments whose building name includes the substring
‘Watson’.” This query can be written as:

select dept_name
from department
where building like ’%Watson%’;

For patterns to include the special pattern characters (that is, % and ), SQL allows
the specification of an escape character. The escape character is used immediately
before a special pattern character to indicate that the special pattern character
is to be treated like a normal character. We define the escape character for a
like comparison using the escape keyword. To illustrate, consider the following
patterns, which use a backslash (\) as the escape character:

• like ’ab\%cd%’ escape ’\’ matches all strings beginning with “ab%cd”.

• like ’ab\\cd%’ escape ’\’ matches all strings beginning with “ab\cd”.

SQL allows us to search for mismatches instead of matches by using the not
like comparison operator. Some databases provide variants of the like operation
which do not distinguish lower and upper case.

SQL:1999 also offers a similar to operation, which provides more powerful
pattern matching than the like operation; the syntax for specifying patterns is
similar to that used in Unix regular expressions.

Frequently Asked Questions