String Functions - Learning MySQL and MariaDB - page 221

In addition, LOCATE() accepts an optional argument to indicate the starting point for the
search, which is notavailable in POSITION() .
Another function for searching a stringis FIND_IN_SET() . If you have a string that
contains several pieces of data separated by commas, this function tells you which ele-
ment in that set of data contains the search pattern you give it. To understand this better,
suppose that we want to get a list of members from Russia, but ordered by the date when
the members joined. We would enter this:
SELECT human_id,
CONCAT(name_first, SPACE(1), name_last) AS Name,
join_date
FROM humans
WHERE country_id = 'ru'
ORDER BY join_date;
+----------+-------------------+------------+
| human_id | Name | join_date |
+----------+-------------------+------------+
| 19 | Elena Bokova | 2011-05-21 |
| 27 | Anahit Vanetsyan | 2011-10-01 |
| 26 | Katerina Smirnova | 2012-02-01 |
+----------+-------------------+------------+
Now suppose that we want to know the position of the member Anahit Vanetsyan in the
list of Russian members. We can see easily from the results just shown that she is the third
member from Russia to join. That's because there are very few results here. Imagine if the
results contained hundreds of names. We could use FIND_IN_SET() with asubquery to
determine this:
SELECT FIND_IN_SET('Anahit Vanetsyan', Names) AS Position
FROM
(SELECT GROUP_CONCAT(Name ORDER BY join_date) AS Names
FROM
( SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
join_date
FROM humans
WHERE country_id = 'ru')
AS derived_1 )
AS derived_2;
+----------+
| Position |
+----------+