34,333
edits
Changes
→Matching by Text Position
+--------------+
2 rows in set (0.00 sec)
</pre>
We can also find instances where string of characters are a separate word. For example if we search for the word 'one' we might get the following:
<pre>
mysql> SELECT prod_name FROM product WHERE prod_name REGEXP 'one';
+----------------------+
| prod_name |
+----------------------+
| Apple iPhone 8Gb |
| One2One USB Hub |
| Level One Firewall |
+----------------------+
3 rows in set (0.00 sec)
</pre>
As we can see from the above example, because the word 'Phone' contains the word 'one' we have retrieved more rows than we anticipated. Using the beginning and and end of word metacharacters we can isolate the instances of 'one' which are a standalone word:
mysql> SELECT prod_name FROM product WHERE prod_name REGEXP '[[:<:]]One[[:>:]]';
+----------------------+
| prod_name |
+----------------------+
| Level One Firewall |
+----------------------+
1 row in set (0.00 sec)
</pre>
