MySQL Cookbook

Chapter 4. Working with Strings

Introduction

Like most data types, strings can be compared for equality or inequality or relative ordering. However, strings have some additional properties to consider:

• Strings can be case sensitive (or not), which can affect the outcome of string operations.

• You can compare entire strings, or just parts of them by extracting substrings.

• You can apply pattern-matching operations to look for strings that have a certain structure.

This chapter discusses several useful string operations you can perform, including how to account for whether or not strings are case sensitive.

The following table, metal, is used in several sections of this chapter:

mysql> SELECT * FROM metal;
+----------+
| name     |
+----------+
| copper   |
| gold     |
| iron     |
| lead     |
| mercury  |
| platinum |
| silver   |
| tin      |
+----------+

The table is very simple, containing only a single string column:

CREATE TABLE metal
(
    name    VARCHAR(20)
);

You can create the table using the metal.sql script in the tables directory of the recipes distribution.

Types of Strings

MySQL can operate on regular strings or binary strings. “Binary” in this context has little to do with the presence of non-ASCII values, so it’s useful right at the outset to make a distinction:

• Binary data may contain bytes that lie outside the usual range of printable ASCII characters.

• A binary string in MySQL is one that MySQL treats as case sensitive in comparisons. For binary strings, the characters A and a are considered different. For non-binary strings, they’re considered the same.

A binary column type is one that contains binary strings. Some of MySQL’s column types are binary (case sensitive) and others are not, as illustrated here:

Column type	Binary/case sensitive
CHAR, VARCHAR	No
CHAR BINARY, VARCHAR BINARY	Yes
TEXT	No
BLOB	Yes
ENUM, SET	No