MySQL fuzzy query like wildcard character

MySQL provides standard SQL pattern matching, as well as an extended regular expression pattern matching format based on Unix utilities like VI, grep, and sed.

First, SQL mode

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

SELECT field from table WHERE a field like condition

With regard to the conditions, SQL provides four matching modes:

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

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

will be u_name for "Zhang San", "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 of "three" and "cat" in U_name, use the and condition

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

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

Although can search out "three feet cat", but can not search out the eligible "Zhang Cat three".

2,_: Represents any single character. Matches a single arbitrary character, which is commonly used to restrict the expression's character-length statement: (which can represent one Chinese character)

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

Only find "Tang Sanzang" so u_name for three words and the middle of a word is "three";

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

Just find out "three-legged cat" this name is three words and the first word is "three";

Second, the regular mode

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

Some of the characters that extend the regular expression are:

“.” matches any single character. (Single-byte characters)

A 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, while "[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 types of writing. For example, "[AA]" matches lowercase or uppercase "a" and "[A-za-z]" matches any letter of two notation.

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

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

To illustrate how an extended regular expression works, the like query shown above uses RegExp rewrite below:

To find the name starting with "three", use "^" to match the beginning of the name.

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

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

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 "Zhang San", "Zhang Cat three" and so on "three" end of the records are all found out.

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

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

Note: If you have Chinese characters, you may need to be aware of them when you use them.

Finally, fill in the SQL used in the project:

SELECT * from ' z_2015-08-27 ' where deviceid= ' 460040206202629 ' and speed not REGEXP ' ^0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0 /‘

SELECT * from ' z_2015-08-27 ' where deviceid= ' 460040206202629 ' and speed not REGEXP ' 0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/ ' #这句好些, using

Select positions from ' z_2015-08-27 ' where deviceid= ' 460040206202629 ' and sendingtime>= ' 00:00 ' and sendingtime<= ' 23:59 ' and speed not REGEXP ' 0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/0/';;

MySQL fuzzy query like wildcard character