Database Reference
In-Depth Information
Chapter 10. String Functions
A string is a value that cancontain alphabetical characters, digits, and other characters
(e.g., the ampersand, the dollar sign). Although a string can contain numbers, they are not
considered numeric values. It's a matter of context and perspective. For instance, postal
codes in the United States are all digits, but you shouldn't store them as integers because
the postal code for 02138 would become 2138. You should use a string to store the postal
code.
To make the handling of strings easier, MySQL provides many built-in functions. You can
format text for nicer results, make better expressions in a
WHERE
clause, or otherwise ex-
tract and manipulate data from a string or column. Therefore, in this chapter, we'll go
through several string functions, grouping them by similar features, and provide examples
of how they might be used.
BASIC RULES FOR USING FUNCTIONS
There are a fewthings to remember when using functions. String functions also have some conventions of
their own. Some of these rules can be different depending on how your server is configured:
▪ The basic syntax of a function is to a keyword immediately followed by arguments in paren-
theses. You cannot generally have a space between the keyword and the opening parenthesis like
you can with operators in SQL statements (e.g.,
IN ()
within a
WHERE
clause).
▪ Some functions take noarguments, such as
NOW()
, which returns the current date or time. Oth-
er functions accept a particular number of arguments. Arguments are generally separated by
commas, and some arguments can be augmented with keywords.
▪ When you pass text as an argument to a string function, put the text in single or double quotes.
▪ When giving a column as an argument, you generally don't use single quotes around the column
name — if you do, MySQL will think you mean the literal text given. You can use backticks
around the column name if the name is a reserved word or contains a character that might cause
other problems.
▪ If by chance a string function tries to return a value that is larger (i.e., more characters) than al-
lowed by the system settings (set by the
max_allowed_packet
configuration option),
MySQL will return NULL instead.
▪ Some arguments to string functions represent positions within the strings. The first character in
a string is numbered 1, not 0. Some functions let you count back from the end of the string, us-
ing negative integers. In these arguments, -1 refers to the last character.
▪ Some string functions call for a character length as an argument. If you give a fractional value to
these functions, MySQL will round that value to the nearest integer.
