Database MySQL wildcards and pattern matching regular expressions

Today, a colleague said that he wrote a sql statement that there was no result in the query.

SELECT * FROM Persons WHERE City LIKE'[BLN]%'

Then let's do it again:

SQL wildcards are statements that use special characters or grammatical spelling to execute fuzzy search instructions. When querying data in a database, SQL wildcards can replace one or more characters. Must be used with the LIKE operator. The wildcards that can be used in SQL are:

Wildcard character

Description%

Replace one or more characters

-replace only one character [charlist] any single character in the character column [^ charlist] or [! charlist] that is not in the character column

"%": matches any number of characters (zero or more); "_": matches any single character

You can use LIKE or NOT LIKE comparison operators when using wildcards, not = or! =

Take user table as an example:

Query records ending in a: select * from user where username like'% a'

Query the user name starting with a: select * from user where username like'a%'

The query does not start with a user name: select * from user where username like'a%'

Query the user name containing a: select * from user where username like'% a%'

Query a user name with two characters: select * from user where username like'_ _'

The point is here:

Other types of MySQL pattern matching are extended regular expressions, using the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, and then you can check LIKE and RLIKE yourself when you encounter HIVE).

Some of the characters that extend regular expressions are:

"." Matches any single character, a character class "[.]" Match any characters in brackets such as "[abc]" matches "a", "b" or "c". To name a range of characters, use a connector "-". "[amurz]" matches any lowercase letter, while "[0-9]" matches any number.

"*" matches zero or more things in front of it. For example, "x*" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ". *" matches any number of characters.

In addition, regular expressions are case-sensitive, of course, you can use a character class colleague to match case such as "[aA]" matches lowercase or uppercase "a" and "[a-zA-Z]" matches any letter of both.

To locate a pattern to match the beginning or end of the target value, use "^" at the beginning of the pattern or "$" at the end of the pattern. Here is how to write a LIKE query using REGEXP:

Query city for information about people who start with "L", use "^" to match the beginning of the name and "[lL]" to match lowercase or uppercase "l"

Select * from Persons where City REGEXP "^ [lL]"

Query the person whose lastname ends with "sh" and use "$" to match the end of the name:

SELECT * FROM Persons WHERE LastName REGEXP "sh$"

Query lastname contains an "A" person, using "[aA]" to match lowercase or uppercase "a":

SELECT * FROM Persons WHERE LastName REGEXP "[aA]"

Query a record with exactly 6 characters in the city name, using "^" and "$" to match the beginning and end of the name, and 6 "." The instance is between the two:

SELECT * FROM Persons WHERE city REGEXP "^. $"

You can also use the "{n}"repeat n times" operator:

SELECT * FROM pet WHERE name REGEXP "^. {6} $"