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:

 name VARCHAR(20)

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

4.1.1 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











Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: