Mysql like query string Example Statement

MySQL provides standard SQL mode matching and an extended regular expression mode matching format based on Unix utilities such as vi, grep, and sed.

I. SQL Mode

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:

1, %: represents any or multiple characters. It can match characters of any type and length.

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 ".

2, _: represents any single character. Matches any character. It is often used to limit the character length of an expression: (can represent a Chinese character)

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} $ ';

NOTE: If it is a Chinese character, you may need to pay attention to it during use.

The following are some examples:

Generally, mysql in queries can be written like this.

Copy codeThe Code is as follows:
SELECT *
FROM 'tb _ require'
WHERE 'require _ id'
IN (23,102 4)

This method is generally applicable to the numeric type. If it is a string, single quotation marks must be added. For example:

Copy codeThe Code is as follows:
SELECT *
FROM 'tb _ require'
WHERE 'require _ name'
IN ('aaa', 'bbbbbb ')

If you want to fuzzy match a string, you can use like to add %. For example:

Copy codeThe Code is as follows:
SELECT *
FROM 'tb _ require'
WHERE 'require _ name' LIKE '% aaa %'


What if we need to fuzzy query multiple strings? How do I write if I like to add in?
In this case, you can use the mysql CONCAT function.

Copy codeThe Code is as follows:
SELECT * FROM MERs
WHERE 'Robert Bob Smith III, PhD. 'like concat (' % ', name,' % ')


This solves the like in problem.

Note that CONCAT must be followed by parentheses without spaces. If there is space, an error may be reported.

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:

1, %: represents any or multiple characters. It can match characters of any type and length.

For example

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
SELECT * FROM [user] WHERE u_name LIKE '% 3%' AND u_name LIKE '% cat %'

If you use

Copy codeThe Code is as follows:
SELECT * FROM [user] WHERE u_name LIKE '% 3% cat %'

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

2, _: represents any single character. Matches any character. It is often used to limit the character length of an expression: (can represent a Chinese character)

For example

Copy codeThe Code is as follows:
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

Copy codeThe Code is as follows:
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;