Database Reference
In-Depth Information
LIMIT 5;
+-------------------------+---------------+
| Species | Family |
+-------------------------+---------------+
| apolinar's wren | TROGLODYTIDAE |
| band-backed wren | TROGLODYTIDAE |
| banded wren | TROGLODYTIDAE |
| bar-winged wood-wren | TROGLODYTIDAE |
| bar-winged wren-babbler | TIMALIIDAE |
+-------------------------+---------------+
The
QUOTE()
function takes astring and returns it enclosed in single quotes. But it does
a good deal more: it makes it input-safe by marking certain characters that could cause
trouble in SQL statements or other programming languages. These characters are single
quotes, backslashes, null (zero) bytes, and Ctrl-Z characters. The
QUOTE()
function pre-
cedes each of these with a backslash so that they won't be interpreted in some way or (in
the case of a single quote) cause SQL to prematurely terminate the string.
In the following example, we're selecting a list of bird species named for a
Prince
or
Princess
:
SELECT QUOTE(common_name)
FROM birds
WHERE common_name LIKE "%Prince%"
ORDER BY common_name;
+----------------------------------+
| QUOTE(common_name) |
+----------------------------------+
| 'Prince Henry\'s Laughingthrush' |
| 'Prince Ruspoli\'s Turaco' |
| 'Princess Parrot' |
+----------------------------------+
Notice in the results that because of the
QUOTE()
function, the strings returned are en-
closed in single quotes, and any single quotes within the strings are escaped with a back-
slash. This can prevent errors if the value is passed to another program.
Trimming and Padding Strings
One of the problemswith allowing the public to enter data into a website is that they're
not always careful. They do things like adding spaces before and after the text. There are a
few functions for trimming anyleading or trailing spaces from the values of a column.
The
LTRIM()
function eliminates any leading spaces to the left. For columns with spaces
