String Expressions

Literal strings in expressions are written as quoted values. By default, either single quotes or double quotes can be used, although single quotes are more standard. Also, if the ANSI_QUOTES SQL mode is enabled, double quotes are interpreted as identifier-quoting characters, so literal strings can be quoted only with single quotes.

The data types for representing strings in tables include CHAR, VARCHAR and the TEXT and BLOB types. The type chosen to be used depends on factors such as the maximum length of values and whether fixed-length or variable-length values are required.

Direct use of strings in expressions occurs primarily in comparison operations. Otherwise, most string operations are performed by using functions.

The usual comparison operators apply to string values (=, <>, <, BETWEEN, and so forth):

Operator Definition

< Less than

<= Less than or equal to

= Equal to

<=> Equal to (works even for NULL values)

<> or != Not equal to

>= Greater than or equal to

> Greater than

BETWEEN

<x AND Y>

Indicate a range of numerical values

CONCAT

String concatenation is done with the CONCAT() function:

mysql> SELECT CONCAT('abc','def');

Instructor Notes: More examples...

mysql> SELECT concat(name, ' is in ', district) FROM city LIMIT 5;

+---+

OR (||) or CONCAT

The || operator is treated as the logical OR operator by default, but can be used for string concatenation if PIPES_AS_CONCAT SQL mode is enabled:

mysql> SELECT 'abc' || 'def';

+---+

| 'abc' || 'def' | +---+

| 0 | +---+

1 row in set, 2 warnings (#.## sec)

mysql> SET sql_mode = 'PIPES_AS_CONCAT';

Query OK, 0 rows affected (#.## sec)

mysql> SELECT 'abc' || 'def';

+---+

| 'abc' || 'def' | +---+

| abcdef | +---+

In the first SELECT statement, || performs a logical OR operation. This is a numeric operation, so MySQL converts the strings in the expression to numbers first. Neither looks like a number, so MySQL converts them to zero, which is why there is a warning count of two. The resulting operands for the operation are zero, so the result also is zero. After PIPES_AS_CONCAT is enabled, || produces a string concatenation instead.

117

Case Sensitivity in String Comparisons

String comparisons are somewhat more complex than numeric or temporal comparisons. A letter in uppercase may compare as the same or different than the same letter in lowercase, and a letter with one type of accent may be considered the same or different than that letter with another type of accent.

• Character Sets and Collations - String expressions contain characters from a particular character set, which is associated with one of the collations (sorting orders) available for the character set.

Characters may consist of single or multiple bytes. A collation can be case insensitive (lettercase is not significant) or case sensitive (lettercase is significant).

The rules that govern string comparison apply in several ways. They determine the result of comparisons performed explicitly with operators such as = and <, and comparisons performed implicitly by ORDER BY, GROUP BY, and DISTINCT operations.

The default character set and collation for literal strings depend on the values of the character_set_connection and collation_connection system variables. The default character set is latin1. The default collation is latin1_swedish_ci, which is case insensitive as indicated by the “_ci” at the end of the collation name. Assuming these connection settings, literal strings are not case sensitive by default. The comparing strings that differ only in lettercase can be seen in the following example:

mysql> SELECT 'Hello' = 'hello';

+---+

| 'Hello' = 'hello' | +---+

| 1 | +---+

A given collation might cause certain accented characters to compare the same as other characters. For example, 'ü' and 'ue' are different in the default latin1_swedish_ci collation, but with the latin1_german2_ci collation (“German phone-book” collation), they have the same sort value and thus compare as equal:

mysql> SELECT 'Müller' = 'Mueller';

+---+

| 'Müller' = 'Mueller' | +---+

| 0 | +---+

mysql> SET collation_connection = latin1_german2_ci;

mysql> SELECT 'Müller' = 'Mueller';

+---+

Using LIKE for Pattern Matching

Operators such as = and != are useful for finding values that are equal to or not equal to a specific exact comparison value. When it's necessary to find values based on similarity instead, a pattern match is useful.

To perform a pattern match, use value LIKE 'pattern', where value is the value to test and 'pattern' is a pattern string that describes the general form of values to match.

Patterns used with the LIKE pattern-matching operator can contain two special characters (called

“metacharacters” or “wildcards”) that stand for something other than themselves:

• Percent (%) Character - The percent character matches any sequence of zero or more characters.

For example, the pattern 'a%' matches any string that begins with 'a', '%b' matches any string that ends with 'b', and '%c%' matches any string that contains a 'c'. The pattern '%' matches any string, including empty strings.

• Underscore ( '_' ) Character - The underscore character matches any single character. 'd_g' matches strings such as 'dig', 'dog', and 'd@g'. Because '_' matches any single character, it matches itself and the pattern 'd_g' also matches the string 'd_g'.

A pattern can use these metacharacters in combination. For example, '_%' matches any string containing at least one character.

For example, to list all countries with names that start with 'United';

mysql> SELECT Name FROM Country -> WHERE Name LIKE 'United%';

| United States Minor Outlying Islands |

| United States | +---+

To invert a pattern match, use NOT LIKE rather than LIKE:

mysql> SELECT Name FROM Country -> WHERE Name NOT LIKE 'United%';

Note: The NOT LIKE results in a list which is 4 short of the total Country list (239), since the specified pattern matched items have been left out.