Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of the regular expression search function in mysql, which is very detailed and has a certain reference value. Interested friends must read it!

The details are as follows:

We know that regular expressions are special strings that describe search patterns. It is a powerful tool that provides us with a concise and flexible way to recognize pattern-based text characters, such as characters, words, etc. For example, we can use regular expressions to search for e-mail, IP addresses, phone numbers, social security numbers, or anything with a specific pattern. Regular expressions, which can interpret their own syntax by regular expression processors, are widely used on most platforms, from programming languages to databases, including MySQL. The advantage of using regular expressions is that it is not limited to searching for strings based on fixed patterns with percent signs (%) and underscores (_) in the like operator. With regular expressions, there are more metacharacters to construct flexible patterns.

Knowing this, do we want to test the water? Needless to say, MySQL allows you to use the REGEXP operator to match patterns in SQL statements, so let's take a look at the syntax format in the regular where clause:

SELECT column_listFROM table_nameWHERE string_column REGEXP pattern

The string_column in this sql matches the pattern pattern, and if the value in string_column matches the pattern pattern, the expression in the WHERE clause returns 1, otherwise 0. If string_column or pattern is NULL, the result is NULL. In addition to the REGEXP operator, you can use the RLIKE operator, which is synonymous with the REGEXP operator. Where the negative form of the REGEXP operator is NOT REGEXP.

When we're done, let's take a look at an example, suppose we want to find a product whose name starts with the letter Ameno B or C. You can use regular expressions in select statements as follows:

SELECT productnameFROM productsWHERE productname REGEXP'^ (A | B | C) 'ORDER BY productname

Execute the above query statement and get the following results:

+-- + | productname | +-+ | America West Airlines B757200 | | American Airlines: B767-300 | | American Airlines: MD-11S | | ATA: B757300 | | Boeing Xmi 32A JSF | | Collectable Wooden Train | | Corsair F4U (Bird Cage) | +- -- + 7 rows in set

This pattern allows you to query for products whose names start with A _ Magi B or C, where the character ^ indicates a match from the beginning of the string, and the finish character | if there is no match, search for an alternative. The following table describes some of the metacharacters and constructs commonly used in regular expressions:

Metacharacter behavior ^ matches position at the beginning of search string $matches position at the end of search string. Match any single character [...] Match any character in brackets [^...] Match any character not specified in formula parentheses to match p1 or p2 pattern * match previous character zero or more times + previous character one or more times {n} match n instances of the first few characters {mfocus n} instance match from m to n previous character

Let's look for products whose names start with a. We match them with "^" at the beginning of the name, as shown in the query statement:

SELECT productnameFROM productsWHERE productname REGEXP'^ a'

Execute the above query statement and get the following results:

+-- + | productname | +-+ | American Airlines: B767-300 | | America West Airlines B757200 | | ATA: B757300 | | American Airlines: MD-11S | +- -+ 4 rows in set

If you want the REGEXP operator to compare strings in a case-sensitive manner, you can use the BINARY operator to convert strings to binary strings. Because MySQL compares binary bytes byte by byte rather than character by character. This allows string comparisons to be case sensitive. For example, the following statement matches only product names that begin with an uppercase "C":

SELECT productnameFROM productsWHERE productname REGEXP BINARY'^ C'

Execute the above query statement and get the following results:

+-- + | productname | +-- + | Collectable Wooden Train | | Corsair F4U (Bird Cage) | +-- + 2 rows in set

To find a product that ends in f, we can use'$f'to match the end of the string:

SELECT productnameFROM productsWHERE productname REGEXP 'favored'

Execute the above query statement and get the following results:

+-+ | productname | +-+ | Boeing Xmuri 32A JSF | +-+ 1 row in set

To find a product whose name contains "ford", use the following query:

SELECT productnameFROM productsWHERE productname REGEXP 'ford'

Execute the above query statement and get the following results:

+-- + | productname | +-+ | 1968 Ford Mustang | | 1969 Ford Falcon | | 1940 Ford Pickup Truck | | 1911 Ford Town Car | | 1932 Model A Ford J-Coupe | | 1926 Ford Fire Engine | | 1913 Ford Model T Speedster | | 1934 Ford | V8 Coupe | | 1903 Ford Model A | | 1976 Ford Gran Torino | | 1940s Ford truck | | 1957 Ford Thunderbird | | 1912 Ford Model T Delivery Wagon | | 1940 Ford Delivery Sedan | | 1928 Ford Phaeton Deluxe | +-+ 15 rows in set

To find a product whose name contains only 10 characters, you can use'^ 'and' $'to match the beginning and end of the product name and repeat any character {10} times as the following query:

SELECT productnameFROM productsWHERE productname REGEXP'^. {10} $'

Execute the above query statement and get the following results:

+-+ | productname | +-+ | HMS Bounty | | Pont Yacht | +-+ 2 rows in set

In fact, this query method is fixed, variable is the regularity you write, so how to use it depends on the ability of your friends.

The above is all the content of the article "sample Analysis of regular expression search function in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.