mysql wildcard (sql advanced filtering)

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!

You may also be interested in:
  • MySQL fuzzy query usage (regular, wildcard, built-in function)
  • Summary of MySQL database like statement wildcard fuzzy query
  • Mysql | Detailed explanation of fuzzy query using wildcards (like,%,_)
  • A brief discussion on the implementation of fuzzy query using wildcards in MySQL
  • A brief discussion on wildcard escape in MySQL fuzzy query
  • MYSQL Must-know Reading Notes Chapter 8: Using Wildcards for Filtering
  • Things to note when using wildcards in MySQL
  • Detailed introduction to the use of MySql like fuzzy query wildcards
  • mysql regular expression LIKE wildcard

<<:  Eight hook functions in the Vue life cycle camera

>>:  canvas.toDataURL image/png error handling method recommendation

Recommend

What magical uses does CSS filter have

background Basic Concepts CSS filter property app...

How to deploy springcloud project with Docker

Table of contents Docker image download Start mys...

How to insert batch data into MySQL database under Node.js

In the project (nodejs), multiple data need to be...

Summary of JavaScript custom object methods

Table of contents 1. Use object to create an obje...

Mysql delete duplicate data to keep the smallest id solution

Search online to delete duplicate data and keep t...

Detailed explanation of docker compose usage

Table of contents Docker Compose usage scenarios ...

Summary of methods for finding and deleting duplicate data in MySQL tables

Sometimes we save a lot of duplicate data in the ...

Summary of Nginx location and proxy_pass path configuration issues

Table of contents 1. Basic configuration of Nginx...

mysql5.7.21.zip installation tutorial

The detailed installation process of mysql5.7.21 ...

Element Timeline implementation

Table of contents Components - Timeline Custom no...

How to enable the slow query log function in MySQL

The MySQL slow query log is very useful for track...