MySQL Regexps
You have seen MySQL pattern matching with LIKE ...%. MySQL supports another type of pattern matching operation based on regular expressions and the REGEXP operator. If you are aware of PHP or PERL, then it‘s very simple for you to understand because this matching is very similar to those scripting regular expressions.
Following is the table of pattern, which can be used along with REGEXP operator.
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
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
Examples:
Now based on above table, you can device various type of SQL queries to meet your requirements. Here, I‘m listing few for your understanding. Consider we have a table called person_tbl and it‘s having a field called name:
Query to find all the names starting with ‘st‘
mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘^st‘;
Query to find all the names ending with ‘ok‘
mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘ok$‘;
Query to find all the names, which contain ‘mar‘
mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘mar‘;
Query to find all the names starting with a vowel and ending with ‘ok‘
mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘^[aeiou]|ok$‘;
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
