YouTip

In previous chapters, we have learned that MySQL can perform fuzzy matching using **LIKE ...%**. MySQL also supports other regular expression matching. In MySQL, the REGEXP and RLIKE operators are used for regular expression matching. If you are familiar with PHP or Perl, this will be very easy to operate, as MySQL's regular expression matching is similar to these scripting languages. The following regular expression patterns can be applied to the REGEXP operator. | Pattern | Description | | --- | --- | | ^ | Matches the start of the input string. If the RegExp object's Multiline property is set, ^ also matches the position after 'n' or 'r'. | | $ | Matches the end of the input string. If the RegExp object's Multiline property is set, $ also matches the position before 'n' or 'r'. | | . | Matches any single character except "n". To match any character including 'n', use a pattern like '[.n]'. | | [...] | Character set. Matches any one character contained within the set. For example, '' can match the 'a' in "plain". | | [^...] | Negated character set. Matches any character not contained within the set. For example, '[^abc]' can match the 'p' in "plain". | | p1|p2|p3 | Matches p1 or p2 or p3. For example, 'z|food' can match "z" or "food". '(z|f)ood' matches "zood" or "food". | | * | Matches the preceding subexpression zero or more times. For example, zo* can match "z" as well as "zoo". * is equivalent to {0,}. | | + | Matches the preceding subexpression one or more times. For example, 'zo+' can match "zo" and "zoo", but not "z". + is equivalent to {1,}. | | {n} | n is a non-negative integer. Matches exactly n times. For example, 'o{2}' cannot match the 'o' in "Bob", but can match the two o's in "food". | | {n,m} | m and n are non-negative integers, where n SELECT name FROM person_tbl WHERE name REGEXP '^st'; Find all data in the name field that ends with **'ok'**: mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$'; Find all data in the name field that contains the string **'mar'**: mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar'; Find all data in the name field that starts with a vowel character or ends with the string **'ok'**: mysql> SELECT name FROM person_tbl WHERE name REGEXP '^|ok$'; Select records from the orders table where the description contains "item" followed by one or more digits. SELECT * FROM orders WHERE order_description REGEXP 'item+'; Use the BINARY keyword to make the match case-sensitive: SELECT * FROM products WHERE product_name REGEXP BINARY 'apple'; Use OR for multiple matching conditions. The following will select employee records where the last name is "Smith" or "Johnson": SELECT * FROM employees WHERE last_name REGEXP 'Smith|Johnson'; ### Pattern Matching Using RLIKE RLIKE is the operator in MySQL used for regular expression matching, identical to REGEXP. RLIKE and REGEXP are interchangeable and have no difference. Here is the basic syntax for using RLIKE for regular expression matching: SELECT column1, column2, ... FROM table_name WHERE column_name RLIKE 'pattern'; **Parameter Description:** * `column1`, `column2`, ... are the names of the columns you want to select. Use `*` to select all columns. * `table_name` is the name of the table from which you want to query data. * `column_name` is the name of the column on which you want to perform the regular expression match. * `'pattern'` is a regular expression pattern. SELECT * FROM products WHERE product_name RLIKE '^'; The above SQL statement selects all products whose product name starts with a digit.