mysql wildcard (sql advanced filtering)

First, let's briefly introduce wildcards, special characters used to match part of a value.

search pattern A search condition consisting of literal values, wildcard characters, or a combination of both.

Wildcards are a supplement to operators. Operators are confirmed, while wildcards are fuzzy.

Here are a few examples:

% Wildcard:

SELECT * FROM products
WHERE products.prod_name LIKE 'jet%'

This % means that any preceding character appears any number of times.

This example uses the search式'jet%' . When this clause is executed, any word starting with jet will be searched. % tells MySQL to accept any characters after jet, no matter how many characters it is.

Case Sensitivity Depending on how MySQL is configured, searches can be case sensitive. If case-sensitivity is used, 'jet%' will not match JetPack 1000 .

Let's look at another example account:

SELECT * FROM products
WHERE products.prod_name LIKE '%anvil%'

The above means that any prod name containing the character anvil will be matched.

Again, here is another example:

SELECT * FROM products
WHERE products.prod_name LIKE 's%e'

This means that all prod name starting with s and ending with e will be matched.

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

Note Trailing spaces Trailing spaces may interfere with wildcard matching. For example, in the save word
When anvil , if it is followed by one or more spaces, the WHERE clause
prod_name LIKE '%anvil' will not match them because of the l at the end.
There are extra characters after . A simple way to solve this problem is to append a % to the end of the search pattern.

Note: NULL Although it seems that the % wildcard character can match anything, there is one exception, namely NULL . Even WHERE prod_name LIKE '% ' will not match rows that use the value NULL as the product name.

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

SELECT * FROM products
WHERE products.prod_name LIKE '_ ton anvil'

The _ above represents any character.

Unlike % which can match 0 characters, _ always matches one character, no more and no less.

As you can see, MySQL wildcards are very useful. This functionality comes at a cost, however: wildcard searches generally take longer to process than the other searches discussed previously. Here are some tips to remember when using wildcards.

Don't overuse wildcards. If other operators can achieve the same purpose, you should use other operators.

When you do need to use wildcards, don't use them at the beginning of a search pattern unless absolutely necessary. Placing the wildcard at the beginning of the search pattern is the slowest search.

This is the end of this article about MySQL wildcards (SQL advanced filtering). For more relevant MySQL wildcard content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!