MySql like fuzzy query Usage Details

1. SQL pattern matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters ). In MySQL, the SQL mode is case-insensitive by default. Some examples are shown below. Note that when you use SQL mode, you cannot use = or! =; And use the LIKE or not like comparison operator.

SELECT field FROM table WHERE a field Like Condition

SQL provides four matching modes for conditions:

For example, SELECT * FROM [user] WHERE u_name LIKE '% 3%'

We will find all records with "3", such as "3 Zhang", "3 Zhang", "3 Zhang Mao", and "3 Tang sanzang.

In addition, if you need to find records with "three" and "cat" in u_name, use the and condition.

SELECT * FROM [user] WHERE u_name LIKE '% 3%' AND u_name LIKE '% cat %'

If SELECT * FROM [user] WHERE u_name LIKE '% 3% cat %' is used'

Although three-legged cats can be searched, three-legged cats cannot be searched ".

For example, SELECT * FROM [user] WHERE u_name LIKE '_ 3 _'

Only find that the u_name such as "Tang sanzang" is three characters and the middle word is "three;

For example, SELECT * FROM [user] WHERE u_name LIKE 'three __';

Find out that the name of the "three-legged cat" is three characters and the first word is "three;

 

Ii. Regular Expression Mode

Other types of pattern matching provided by MySQL use extended regular expressions. When you perform a match test on this type of pattern, use the REGEXP and not regexp operators (or RLIKE and not rlike, which are synonyms ).

Some Characters of the extended regular expression are:

"." Matches any single character. (Single-byte characters)

A character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name a range of characters, use a hyphen (-). "[A-z]" matches any lowercase letter, and "[0-9]" matches any number.

"*" Matches zero or multiple items before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ". * "matches any number of things.

Regular Expressions are case-sensitive, but if you want to, you can use one character class matching method. For example, "[aA]" matches lowercase or upper-case "a", and "[a-zA-Z]" matches any letter in either of the two statements.

If it appears anywhere in the tested value, the schema matches (as long as they match the entire value, the SQL schema matches ).

To locate a pattern so that it must match the start or end of the tested value, use "^" at the start of the pattern or "$" at the end of the pattern ".

To demonstrate how the extended regular expression works, the LIKE Query shown above is rewritten using REGEXP below:

To find the name starting with "3", use "^" to match the name.

FROM [user] WHERE u_name REGEXP '^ 3 ';

All records starting with "3", such as "three-legged cats" with u_name will be found.

To find the name ending with "3", use "$" to match the end of the name.

FROM [user] WHERE u_name REGEXP 'three $ ';

All records ending with "3", such as "Zhang San" and "Zhang Mao San", will be found.

You can also use the "{n}" "Repeat n times" operator to rewrite the previous query:

FROM [user] WHERE u_name REGEXP 'B {2} $ ';