Tutorial on using wildcards to filter in mysql

What are wildcards?

Wildcard characters are special characters used to match part of a value.

Search mode: Search conditions composed of literal values, wildcards, or a combination of both.

1.like operator:

All the operators introduced earlier are for constant values filtered. Whether it's matching or multiple values, testing greater or less than a known value, or checking a range of values, the common denominator is that the values ​​used in filtering are known. However, this filtering method is not always easy to use. For example, how to search for all products whose product names contain the text anvil? It's impossible to use simple comparison operators, you must use wildcards. Use wildcards to create search patterns that compare specific data. In this example, if you want to find all products whose names contain anvil, you can construct a wildcard search pattern to find products where anvil appears anywhere in the product name.

To use wildcards in search clauses, the link operator must be used. like instructs MySQL, followed by a search pattern that uses wildcard matching instead of direct equality matching for comparison.

2. Percent (%) wildcard character

The most commonly used wildcard character is percent Number(%). In a search string, % represents any number of occurrences of any character. For example, to find all products that start with the word a, you can use the following select statement:

select prod_id, prod_name from products where prod_name like 'a%';

This example uses search Pattern 'a%'. When executing this sentence, search for any word starting with a. % tells MySQL to accept any character after a, no matter how many characters it has.

Note: Depending on how MySQL is configured, the search can be made case-sensitive. If case sensitive, 'a%' does not match the word Apache.

Wildcards can be used anywhere in the search pattern, and multiple wildcards can be used. The following example uses two wildcards, which are located at both ends of the pattern:

select prod_id, prod_name from products where prod_name like '%a%';

Code analysis: search pattern '%a% ' means match any value that contains the text anvil, regardless of what characters appear before or after it.

Wildcard characters can also appear in the middle of search patterns. For example, the following example:

select prod_namefrom products where prod_name like '%a%';

It is important to note that in addition to one or more characters, % can also match 0 character. % represents 0, 1 or more characters at a given position in the search pattern.

Note: 1. Spaces may interfere with wildcard matching. For example, when there are one or more spaces after a in '%a%', the clause where prod_name like '%a%'; will not match them because there are extra characters after a. A simple way to solve this problem is to add a % at the end of the search pattern. Another better way is to use a function to remove the trailing spaces.

2. Although it seems that the % wildcard can match anything, there is one exception, which is NULL. Even where prod_name like ‘%’ does not match rows with the value NULL as the product name.

3. Underscore '_' wildcard

Another useful wildcard character is the underscore (_ ). Underscore has the same purpose as %, but it matches a single character instead of multiple characters.

For example:

select prod_id,prod_name from products where products where prod_name like '_ ton anvil';

Output:

The search pattern in this where clause gives two wildcard characters followed by text. The results only display lines that match the search pattern: the underline in the first line matches 1, and the underline in the second line matches 2.

You can try using % to match the results returned and compare them.

The above is the detailed content of Tutorial on using wildcards to filter in mysql. For more information, please follow other related articles on the PHP Chinese website!