MySQL in a Nutshell, 2nd Edition

Chapter 11. String Functions

MySQL has several built-in functions for formatting, manipulating, and analyzing strings, both user-specified and within columns of data. This chapter lists these string functions, provides the syntax of each, and gives examples of their use. The examples in this chapter use a fictitious database for a college.

String functions do not change their inputs; the functions’ return values contain the changes.

String Functions Grouped by Type

The list of string functions is quite long, but many perform similar roles. The following list groups the functions by these roles.

Character Sets and Collation

CHARSET(), COALESCE(), COERCIBILITY(), COLLATION().

Converting

ASCII(), BIN(), BINARY, CAST(), CHAR(), COMPRESS(), CONVERT(), EXPORT_SET(), HEX(), MAKE_SET(), ORD(), SOUNDEX(), UNCOMPRESS(), UNHEX().

Formatting

CONCAT(), CONCAT_WS(), LCASE(), LENGTH(), LOWER(), LPAD(), LTRIM(), OCTET_LENGTH(), QUOTE(), RPAD(), RTRIM(), SPACE(), TRIM(), UCASE(), UPPER().

Expressions

BIT_LENGTH(), CRC32(), CHAR_LENGTH(), CHARACTER_LENGTH(), ELT(), FIELD(), FIND_IN_SET(), INSTR(), INTERVAL(), LOCATE(), MATCH() AGAINST(), POSITION(), STRCMP(), UNCOMPRESSED_LENGTH().

Extracting

LEFT(), LOAD_FILE(), MID(), RIGHT(), SUBSTR(), SUBSTRING(), SUBSTRING_INDEX().

Manipulating

INSERT(), REPEAT(), REPLACE(), REVERSE().