Mysql REGEXP
by Jayanthi[ Edit ] 2014-03-05 15:19:47
Mysql REGEXPS
| 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 |
To find the productname starts with 'nat'
SELECT productname FROM product WHERE productname REGEXP '^nat';
To find the productname ends with 'nat'
SELECT productname FROM products WHERE productname REGEXP 'oil$';
To find the productname contains with 'go'
SELECT productname FROM products WHERE productname REGEXP 'go';
To find all the productnames starting with the letters a,k and ending with 'oil'
SELECT productname FROM products WHERE productname REGEXP '^[ak]|oil$';
Strings 'cat','caat','ct','caact' are included in the field.
use * Regexp, it returns to cat,caat,ct only.
select field_name REGEXP "^ca*t";
Use + Regexp, it returns to cat,ct only.
select field_name REGEXP "^ca+t";
