mysql正则表达式使用实例

Summary: in this tutorial, you will learn how to use MySQL regular expression with the SELECT statement to query data based on complex patterns.

Regular expression is a powerful tool that gives you a concise and flexible way to identify strings of text e.g., characters, words…etc based on patterns. For example, you can use regular expressions to search for email, IP address, phone number, social security number or anything that has specific pattern.

Regular expression is also known as regex. Regular expression uses its own syntax that can be interpreted by a regular expression processor. Regular expression is widely used in almost platforms from programming languages to databases including MySQL.

MySQL supports regular expression in the SQL statements, which you can search for strings of text by using special operator called REGEXP.  The following demonstrates the syntax of the REGEXP operator in the WHERE clause:

SELECT column_list  FROM table_name  WHERE column REGEXP pattern

The advantage of using regular expression is that you are not only limited to search for a string based on a fixed pattern like MySQL LIKE operator but also your own patterns.

The disadvantage of using regular expression is that it is quite difficult to understand and maintain such a complicated pattern. Therefore you should describe the meaning of regular expressions in the comments when using it in SQL statements. In some cases, the performance of data retrieval is decreased if you use complicated patterns in regular expression.

MySQL regular expression example

Let’s take a look at an example of using regular expressions in MySQL.

Suppose you want to find out employee whose last name starts with character M, B or T. You can use a regular expression in the SELECT statement as follows:

SELECT lastname, firstname  FROM employees WHERE lastname REGEXP  '^(M|B|T)';

The pattern allows you to find employee whose last name starts with character M, B or T.

• The character ^ means to match from the beginning of the string.

• The character | means to search for alternatives if one fails to match.

In this tutorial, you have learned how to query data from database table using MySQL regular expressions

Related Tutorials

• Using MySQL LIKE Operator to Select Data Based On Patterns

原文链接：http://outofmemory.cn/mysql/tips/mysql-regular-expression-regexp