String Functions - Learning MySQL and MariaDB - page 219

Searching Strings and Using Lengths
MySQL and MariaDB donot have comprehensive functions for searching string based on
patterns. Yes, there's the REGEXP operatorthat permits some pattern matching. But this
isn't as robust and isn't fine tuned as easily as the capabilities offered by programming lan-
guages like PHP and Perl. But there are a few functions that assist in searching strings.
We'll look at some of them in this section.
Locating Text Within a String
MySQL and MariaDB havea few built-in functions that can find characters within a string.
These functions return the location where the search parameter was found.
The LOCATE() functionreturns the numeric starting point just left of the first occurrence
of a given substring in a given string. It does not search beyond this point. Let's look at an
example. Suppose we want a list of Avocet birds — they're a type of shore birds that is part
of the Recurvirostridae family. We could enter something like this:
SELECT common_name AS 'Avocet'
FROM birds
JOIN bird_families USING(family_id)
WHERE bird_families.scientific_name = 'Recurvirostridae'
AND birds.common_name LIKE '%Avocet%';
+-------------------+
| Avocet |
+-------------------+
| Pied Avocet |
| Red-necked Avocet |
| Andean Avocet |
| American Avocet |
+-------------------+
Now suppose we want to eliminate the word Avocet from the names returned. There are a
few ways we might do that: one way is to use the LOCATE() function to find the word
Avocet , and extract all text before it with the SUBSTRING() function:
SELECT
SUBSTRING(common_name, 1, LOCATE(' Avocet', common_name) ) AS 'Avocet'
FROM birds
JOIN bird_families USING(family_id)
WHERE bird_families.scientific_name = 'Recurvirostridae'
AND birds.common_name LIKE '%Avocet%';
+-------------+