Working with Strings - MySQL - page 168

as numbers. For example, to concatenate a SET element to an existing SET column, use
CONCAT() to add the new value to the existing value, preceded by a comma. But re‐
member to account for the possibility that the existing value might be NULL . In that case,
set the column value equal to the new element, without the leading comma:
UPDATE tbl_name
SET set_col = IF ( set_col IS NULL , val , CONCAT ( set_col , ',' , val ));
5.11. Searching for Substrings
Problem
You want to know whether a given string occurs within another string.
Solution
Use LOCATE() or a pattern match.
Discussion
The LOCATE() function takes two arguments representing the substring that you're
looking for and the string in which to look for it. The return value is the position at
which the substring occurs, or 0 if it's not present. An optional third argument may be
given to indicate the position within the string at which to start looking.
mysql> SELECT name, LOCATE('in',name), LOCATE('in',name,3) FROM metal;
+----------+-------------------+---------------------+
| name | LOCATE('in',name) | LOCATE('in',name,3) |
+----------+-------------------+---------------------+
| gold | 0 | 0 |
| iron | 0 | 0 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 5 | 5 |
| tin | 2 | 0 |
+----------+-------------------+---------------------+
To determine only whether the substring is present if you don't care about its position,
an alternative is to use LIKE or REGEXP :
mysql> SELECT name, name LIKE '%in%', name REGEXP 'in' FROM metal;
+----------+------------------+------------------+
| name | name LIKE '%in%' | name REGEXP 'in' |
+----------+------------------+------------------+
| gold | 0 | 0 |
| iron | 0 | 0 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 1 | 1 |