mysql like query string sample statement

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

1. SQL mode

SQL's pattern matching allows you to use "_" to match any single character, and "%" to match any number of characters (including zero). In MySQL, the schema of SQL is case-insensitive by default. Some examples are shown below. Note that when you use SQL mode, you cannot use = or! =; The LIKE or NOT LIKE comparison operators are used instead.

SELECT field FROM table WHERE field Like condition

In terms of conditions, SQL provides four matching modes:

1, % : represents any number of characters. Matches characters of any type and length.

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

All records with "3" will be found for u_name as "sheet 3", "sheet 3", "three-legged cat", "tang 3 hide", etc.

Also, if you need to find records with both "3" and "cat" in u_name, use the and condition

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

If you use SELECT * FROM [user] WHERE u_name LIKE '%3% cat %'

Although it can search for "three-legged cat", it cannot search for "zhang cat 3".

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

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

Only find out "tang 3 Tibet" such that u_name is 3 characters and the middle 1 character is "3";

SELECT * FROM [user] WHERE u_name LIKE '3__';

Find only "three-legged cat" so that name is 3 words and the first word is "3";

2. Regular patterns

The other type of pattern matching provided by MySQL is using extended regular expressions. When you test this type of schema for matching, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

The characters that extend the regular expression are:

". "matches any single character. (single byte character)

1 character class "[...] "Matches any character in 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 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 anything.

Regular expressions are case sensitive, but if you wish, you can use the 1 character class to match both ways. For example, "[aA]" matches the lowercase or uppercase "a" and "[a-zA-Z]" matches any letter written in both ways.

If it appears anywhere in the value being tested, the pattern matches (as long as they match the entire value, 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 show how extended regular expressions work, the LIKE query shown above is rewritten below using REGEXP:

To find names that start with "3", use "^" to match the beginning of the name.

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

All records starting with "3" will be found if u_name is "3-legged cat" and so on.

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

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

Records ending in "3", "cat 3" and so on will be found.

You can also override the previous query using the '{n}' 'n times' operator:

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

Note: if it is a Chinese character, you may need to pay attention to 1.

Here are some examples:

In general, the mysql in query can be written like this

 
SELECT * 
FROM  `tb_require` 
WHERE  `require_id` 
IN ( 23, 1024 )

This method 1 is generally suitable for numeric types, and if it is a string, it should be enclosed with single quotation marks. Such as:

 
SELECT * 
FROM  `tb_require` 
WHERE  `require_name` 
IN ( 'aaa', 'bbbb')

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

SELECT * 
FROM  `tb_require` 
WHERE  `require_name` LIKE  '%aaa%'

What if there is a requirement to obfuscate multiple strings? like plus in, how do you write it?
You can use the CONCAT function for mysql

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

This solves the problem of like in.

It is important to note that CONCAT should be followed by parentheses without Spaces. If there is a space, it may give an error.

Note that when you are using SQL mode, you cannot use = or! =; The LIKE or NOT LIKE comparison operators are used instead.

SELECT field FROM table WHERE certain field Like condition

In terms of conditions, SQL provides four matching modes:

1, % : represents any number of characters. Matches characters of any type and length.

Such as

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

All records with "3" will be found for "u_name", "cat 3", "cat 3", "tang 3 hide", etc.

Also, if you need to find records with both "3" and "cat" in u_name, use the and condition

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

If use

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

Although it can search for "three-legged cat", it cannot search for "zhang cat 3".

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

Such as

SELECT * FROM  [ user ]  WHERE u_name LIKE  ' _3_' 

Only find out "tang 3 Tibet" such that u_name is 3 characters and the middle one is "3";

Again for instance

SELECT * FROM  [ user ]  WHERE u_name LIKE  ' 3__'; 

Find only "three-legged cat", where name is 3 words and the first word is "3";