Recently, I needed a MySQL conditionals that I had not used previously. I needed to add a condition that included an OR condition on a single field. While OR is available in the WHERE conditional, it along with the LIKE filter overly complicated my query. Some OR conditionals become very complicated with the OR conditional. I found that the MySQL function REGEXP was just what the doctor ordered. It opened up a wide variety of options, and simplified the query.
Using REGEXP turned out to be not only easy, but, convenient. We can use regular expressions. If you are used to using regexp, you will love this feature. For example, assume you have fields called`price` and `view` and you want to find records with the price under $300,000 and a `view` of ‘ocean,’ ‘river,’ or ‘lake,’ you can use regular expressions. You may use the following SQL conditional:
WHERE `price` < 400000 AND `view` REGEXP ‘Ocean|River|Lake’
As with other places you can use regexp, this allows you to say you would like the field `view` to contain the Ocean, River or Lake … regardless of whatever else is in that field. Therefore, it references the field once and incorporates the concept of LIKE.
Of course, you could request the field to contain:
- ‘^Ocean$|^River$|^Lake$’ if you wanted one of these words to be the only word in the field.
- ^Ocean|^River|^Lake’ if you want the first word of the field to be Ocean, River or Lake.
- [a-z ]+ if you simply want to know that there is some view defined.
OMG, you can create any of the wonderful expressions that can be built with the patterns from the following table …
Try your hand at it, knowing you have the following features to build your regular expression:
| Pattern | What the pattern matches |
| ^ | Beginning of string |
| $ | End of string |
| . | Any single character |
| […] | Any character listed between the square brackets |
| [^…] | Any character not listed between the square brackets |
| [a-z] | Within[], dash (-) create a range of contiguous characters or numbers. Here we are specifying all characters from a to z and A to Z. MySQL ignores character case when using expressions |
| p1|p2|p3 | Alternation; matches any of the patterns p1, p2, or p3 |
| * | Zero or more instances of preceding element |
| + | One or more instances of preceding element |
| {n} | n instances of preceding element |
| {m,n} | m through n instances of preceding element |
In addition to the patterns used in building a filter, you should know that MySQL recognizes the escapes in strings. Therefore you can use \n,\r,\t,\$,\^,\\, etc to embed special characters in the search and to search for ^,$ and \.*
To finish off this memo, you need to know:
- RLIKE (not to be confused with LIKE) is a synonym for REGEXP in mySQL
- MySQL examines expressions independent of the character case.
- Oracle’s REGEXP does not support escape characters found above.
- You could install LIB_MYSQLUDF_PREGif you wanted an even more powerful expression handling in your mySQL
- You can learn more about LIB_MYSQLUDF_PREG at http://www.regular-expressions.info/mysql.html