Database Reference
In-Depth Information
| Avocet |
+-------------+
| Pied |
| Red-necked |
| Andean |
| American |
+-------------+
That's acumbersome example, but it shows you how you can use
LOCATE()
in conjunc-
tion with other functions to get what you want from a string. Let's look at another ex-
ample.
Earlier in this chapter, in
Trimming and Padding Strings
, we had some examples in-
volving merging data from another bird-watcher group. That included usingthe
TRIM()
function to remove quotes from around the scientific names of birds spotted by people in
that group. Let's use that column again, but assume that it doesn't have quotes. Instead,
the bird species is given with its bird family in this format:
bird species - bird
family
. For this, we can use the
LOCATE()
function to locate the hyphen and then the
SUBSTRING()
to get the family name for the
JOIN
clause in thatearlier example.
Here's just the excerpt from the
JOIN
clause:
…
JOIN
rookery
.
birds
ON
(
scientific_name
=
SUBSTRING
(
science_name
,
LOCATE
(
' - '
,
science_name
) +
3
) );
Let's pull this apart to understand it better. First, let's focus on the inner function, the
LOCATE()
. The search parameter it's given is a hyphen surrounded by spaces. The
sci-
ence_name
column is the string to search. This function will return the position in the
string where the search parameter is found. We're adding 3 to that because the search
parameter is three characters long — in other words,
LOCATE()
gives us the point
before
the separator and we want to get the substring
after
the end of the separator. So the results
of
LOCATE()
+ 3 is given as the starting point for the
SUBSTRING()
function. Because
we're not specifying how many characters we want, MySQL will extract the remaining
characters. That will give us the scientific name of the bird in the table we're joining to
birds
.
The
POSITION()
functionworks like
LOCATE()
, except that it takes the keyword
IN
instead of a comma between the substring you're searching for and the containing string:
POSITION
(
' - '
IN
science_name
)
