Tutorial on using regular expressions to search in mysql (1)

This topic will learn how to use regular expressions in the MySQL where clause to control data filtering.

Introduction to regular expressions

The filtering examples in the first two chapters allow matching, comparison, and wildcard operators to find data. For basic filtering (or even some not-so-basic filtering), this is enough. But as the complexity of the filter conditions increases, the complexity of the where clause itself also increases.

This is where regular expressions become useful. Regular expressions are special strings (sets of characters) used to match text. If you want to extract phone numbers from a text file, you can use regular expressions. If you need to find all files with a number in the middle of their name, you can use a regular expression. If you want to find all repeated words in a text section, you can use regular expressions. If you want all URLs in a page to be the actual html links of those URLs, you can also use regular expressions.

All programming languages, text editors, operating systems, etc. support regular expressions. Programmers and network administrators have been paying attention to regular expressions as an important part of their technical tools for a long time.

Regular expressions are created using the regular expression language, a special language used to do all the work just discussed and more. Like any language, regular expressions have your Special syntax and instructions must be learned.

Since regular expressions are so useful, what is the relationship between regular expressions and MySQL? As mentioned before, the function of regular expressions is to match text, comparing a pattern (regular expression) with a text string. MySQL provides preliminary support for regular expressions using the where clause, allowing you to specify regular expressions to filter the data retrieved by select.

Using MySQL regular expressions

Basic character matching

We start with a very simple example. The following statement retrieves all rows where column prod_name contains the text 1000:

select prod_name from products where prod_name regexp '1000' order by prod_name;

except key This statement looks very much like a statement using like, except that the word like is replaced by regexp. It tells MySQL that what follows regexp is treated as a regular expression (a regular expression that matches the text body 1000).

Why bother using regular expressions? In the previous example, regular expressions really didn't bring much benefit, but consider the following example:

select prod_name from products where prod_name regexp '.000'order by prod_name;

The regular expression .000 is used here. . is a special character in the regular expression language. It means matching any character. Therefore, 1000 and 2000 both match and return

. Of course, this special example can also be completed using like and wildcard characters.

There is an important difference between like and regexp. Please look at the following two statements:

select prod_name from products where prod_name like '1000' order by prod_name;

select prod_name from products where prod_name regexp '1000' order by prod_name;

If you execute the above two statements, you will find that the first statement does not return data, but the second statement returns a row. Why?

like matches the entire column. If the matched text appears in a column value, like will not find it and the corresponding row will not be returned (unless wildcards are used). And regexp matches within the column value. If the matched text appears in the column value, regexp will find it and the corresponding row will be returned. This is a very important difference.

So, can regexp be used to match the entire column value and play the same role as like? The answer is fixed, just use the ^ and $ locators.

The above is the detailed content of Tutorial on using regular expressions to search in mysql (1). For more information, please follow other related articles on the PHP Chinese website!