返回顶部

OutOfMemory.CN技术专栏-> MySQL-> MySQL技巧-> mysql正则表达式使用实例

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)';

mysql regular expression
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

推荐阅读:
支持

0

反对

0

发表评论