MySQL like query string sample statement _mysql

MySQL provides standard SQL pattern matching and a format for extended regular expression pattern matching based on Unix utilities like VI, grep, and SED

One, SQL mode

SQL pattern matching allows you to match any single character with "_", while "%" matches any number of characters (including 0 characters). In MySQL, the SQL schema defaults to ignoring the case. Some examples are shown below. Note that when you use SQL mode, you cannot use = or! =; use like or not to compare operators.

SELECT field from table WHERE a field like condition

With regard to conditions, SQL provides four matching modes:

1,%: Represents any one or more characters. Can match characters of any type and length.

For example, SELECT * from [user] WHERE u_name like '% III '

will be u_name as "John", "Zhang Cat Three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.

Also, if you need to find a record in u_name that has "three" and "cat", use the and condition

SELECT * FROM [user] WHERE u_name like '% III ' and u_name like '% Cat '

If you use SELECT * from [user] WHERE u_name like '% cat% '

Although can search out "three-legged cat", but can not search out the conditions of the "cat three".

2,_: Represents any single character. Matches a single arbitrary character that is used to restrict the character-length statements of an expression: (you can represent a Chinese character)

For example, SELECT * from [user] WHERE u_name like ' _ Three _ '

Only find "Tang Sanzang" so U_name is three words and the middle one word is "three";

Another example is SELECT * from [user] WHERE u_name like ' three __ ';

Only find "three-legged cat" so name is three words and the first word is "three";

Second, regular mode

Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you match this pattern to a test, 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)

One character class [...] ] "matches any character within the square brackets. For example, "[ABC]" matches "a", "B", or "C". To name a range of characters, use a "-". "[A-z]" matches any lowercase letter, and "[0-9]" matches any number.

"*" matches 0 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 anything.

Regular expressions are case-sensitive, but if you want to, you can use a character class to match two ways of writing. For example, "[AA]" matches either lowercase or uppercase "a" and "[A-za-z]" matches any of the letters in both ways.

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

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

To illustrate how extended regular expressions work, the like query shown below uses RegExp overrides:

To find a name that begins with "three", use "^" to match the beginning of the name.

From [user] WHERE u_name REGEXP ' ^ III ';

will be u_name for the "three-legged cat" and so on "three" records of the beginning of all find out.

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

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

will be u_name as "John", "Zhang Cat three" and so on "three" end of the record all found out.

You can also rewrite the previous query by using the "{n}" "Repeat N-times" operator:

From [user] WHERE u_name REGEXP ' b{2}$ ';

Note: If you are a Chinese character, you may need to pay attention when you use it.

Here are some examples:

In general, use the MySQL in query to write this

Copy Code code as follows:

SELECT *
From ' Tb_require '
WHERE ' require_id '
In (23, 1024)

This method is generally suitable for numeric types, and if it is a string, enclose the single quotation mark. Such as:

Copy Code code as follows:

SELECT *
From ' Tb_require '
WHERE ' Require_name '
In (' AAA ', ' bbbb ')

When querying a string, you can use like plus% if you want to blur the match. Such as:

Copy Code code as follows:

SELECT *
From ' Tb_require '
WHERE ' require_name ' like '%aaa% '

What if there is a need to query multiple strings with ambiguity? Like Plus in, how to write?
This time you can use the MySQL concat function

Copy Code code as follows:

SELECT * FROM Customers
WHERE ' Robert Bob Smith III, PhD. ' Like CONCAT ('% ', name, '% ')

This solves the like-in problem.

Note that the concat need to be followed by parentheses, do not have spaces, there are spaces, may be the error OH.

Note that when you use SQL mode, you cannot use = or! =; use like or not to compare operators.

SELECT field from table WHERE a field like condition

With regard to conditions, SQL provides four matching modes:

1,%: Represents any one or more characters. Can match characters of any type and length.

Like what

Copy Code code as follows:

SELECT * FROM [user] WHERE u_name like '% III '

will be u_name as "John", "Zhang Cat Three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.

Also, if you need to find a record in u_name that has "three" and "cat", use the and condition

Copy Code code as follows:

SELECT * FROM [user] WHERE u_name like '% III ' and u_name like '% Cat '

If you use

Copy Code code as follows:

SELECT * FROM [user] WHERE u_name like '% three% cat '

Although can search out "three-legged cat", but can not search out the conditions of the "cat three".

2,_: Represents any single character. Matches a single arbitrary character that is used to restrict the character-length statements of an expression: (you can represent a Chinese character)

Like what

Copy Code code as follows:

SELECT * FROM [user] WHERE u_name like ' _ Three _ '

Only find "Tang Sanzang" so U_name is three words and the middle one word is "three";

Another example

Copy Code code as follows:

SELECT * FROM [user] WHERE u_name like ' three __ ';

Only find "three-legged cat" so name is three words and the first word is "three";