MySQL must know-8th chapter-Filter with wildcard characters

The eighth chapter is filtered with a wildcard character

This chapter describes what wildcards are, how to use wildcards, and how to use the LIKE operator for a wildcard search to make complex filtering of data.

8.1 Like operator

All of the operators described above are filtered for known values. Whether you match one or more values, test for greater than or less than a known value, or check a range of values, the common denominator is that the values used in the filter are known. However, this filtering method is not always useful. For example, how do I search for all products with text anvil in the product name? With simple comparison operators, you must use a wildcard character. Wildcard characters are used to create search patterns that compare specific data. In this example, if you want to find out all the products with the name anvil, you can construct a wildcard search pattern to find out what anvil products appear anywhere in the product name.
A wildcard character (wildcard) is used to match the special characters of a part of a value.
Search pattern a search condition consisting of a literal value, a wildcard character, or a combination of both.
The wildcard itself is actually a character that has special meanings in the WHERE clause of SQL, and SQL supports several wildcard characters.
To use wildcard characters in a search clause, you must use the LIKE operator. Like indicates MySQL, followed by a search pattern that uses wildcard matching instead of direct equality matching for comparison.
When is the predicate operator not an operator? The answer is when it acts as a predicate (predicate). Technically, like is a predicate rather than an operator. Although the end result is the same, you should be aware of the term in order to avoid encountering this term in a SQL document.

8.1.100 semicolon (%) wildcard character

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

This example uses the search mode ' jet% '. When the sentence is executed, any word that begins with Jet will be retrieved. % tells MySQL to accept any character after jet, regardless of how many characters it has.
Case sensitivity depending on how MySQL is configured, the search can be case-sensitive. If case sensitivity, ' jet% ' and jetpack 1000 will not match.
Wildcards can be used anywhere in search mode, and multiple wildcard characters can be used. The following example uses two wildcard characters, which are located at both ends of the pattern:

Filtering the search pattern with wildcards '%anvil% ' means that matches any position containing the value of the text anvil, regardless of the character that appears before or after it. Wildcard characters can also appear in the middle of the search pattern, although this is not useful. The following example finds all the products with the beginning of s ending with E:

It is important to note that, in addition to one or more characters,% can also match 0 characters. % represents 0, one, or more characters at a given position in the search pattern.
Note trailing space trailing spaces may interfere with wildcard matching. For example, when you save a word anvil, if there is one or more spaces behind it, the clause WHERE prod_name like '%anvil ' will not match them because there are extra characters after the last L. An easy way to solve this problem is to append a% to the search pattern at the end. A better approach is to use the function (which will be introduced in the 11th chapter) to remove the trailing spaces.
Note NULL Although it seems that the% wildcard can match anything, there is one exception, that is, null. Even where prod_name like '% ' does not match the line with the value NULL as the product name.

8.1.2 Underline (_)

Wildcard characters Another useful wildcard is an underscore (_). The underscore is used in the same way as%, but the underscore matches only a single character instead of multiple characters. To give an example:

The search pattern in this where clause gives the two wildcard characters followed by text. The result shows only the rows matching the search pattern: The first line is underlined in 1, the second line matches 2.: 5 ton Anvil the product does not match, because the search pattern requires matching two wildcards instead of one. In contrast, the following SELECT statement uses the% wildcard character to return three rows of products:

and% can match 0 characters not the same, _ always match a character, can not more nor less.

8.2 Tips for using wildcard characters

As you can see, MySQL's wildcard characters are useful. But this functionality comes at a cost: Wildcard searches typically take longer to process than the other searches discussed earlier. Here are some tips to keep in mind with wildcards.

• Do not use wildcard characters excessively. If other operators can achieve the same purpose, you should use a different operator.
• When you do need to use wildcards, do not use them at the beginning of the search pattern unless absolutely necessary. The wildcard character is placed at the beginning of the search mode, and the search is the slowest.
• Pay close attention to the position of the wildcard character. If misplaced, the desired number of outputs may not be returned.
  In short, wildcards are a very important and useful search tool that we often use in the future.

8.3 Summary
This chapter describes what wildcard characters are and how to use SQL wildcard characters in the WHERE clause, and also explains that wildcards should be used carefully and not overused.

MySQL must know-8th chapter-Filter with wildcard characters