In SQL queries, fuzzy matching can be achieved by using LIKE operators and wildcards. Common wildcards include: % represents zero or more characters, and \_ (underscore) represents an arbitrary character; LIKE constructs a matching pattern to find data containing substrings, fixed-length partial content or multiple conditions combinations; however, attention should be paid to avoid using % at the beginning, considering case sensitivity, and not relying on LIKE to handle complex searches; in actual applications, NOT or CASE WHEN can be combined to improve flexibility.

In SQL queries, if you want to find data that matches a specific pattern, LIKE operator with Wildcard Characters is a very practical tool. It does not match exactly like the equals number, but allows fuzzy matching of part of the string, which is especially suitable for data filtering and searching.

1. What are the common wildcard characters?

The two most commonly used wildcard characters in SQL are:

• % : represents zero, one or more characters
• _ (underscore): represents any character

For example:

 SELECT * FROM users WHERE name LIKE 'A%';

This statement will return all names starting with the letter A, such as "Alice", "Andrew", etc.

For example:

 SELECT * FROM users WHERE phone LIKE '555-__34';

This matches a phone number like "555-1234" or "555-AB34", where _ only matches one character.

2. How to use wildcards for fuzzy search?

When using LIKE , the key is how to construct a matching pattern. Here are a few common scenarios:

• Find data containing a substring:

   SELECT * FROM products WHERE description LIKE '%book%';

  This allows you to find products with "book" in the description, such as "textbook" or "notebook".

• Part of the content that matches the fixed length:

   SELECT * FROM codes WHERE code LIKE 'ABC_';

  This will match ABC followed by a code with arbitrary characters, such as "ABCD", "ABCE", but will not match "ABCCD".

• Multi-condition combination:

   SELECT * FROM emails WHERE address LIKE '_@%.com';

  This example is used to roughly verify the mailbox format, ensuring that the address ends with ".com" and that the username part has at least one character.

3. Things to note when using wildcards

Although LIKE is convenient, there are some limitations and performance issues that need to be paid attention to:

• Avoid using % at the beginning

  If you write '%' 某字段, for example:

   SELECT * FROM names WHERE name LIKE '%son';

  Then the database cannot effectively utilize indexes and the query speed will slow down. This "left wild-patch" operation should be avoided as much as possible.

• Case sensitivity depends on database settings

  On some systems (such as PostgreSQL), the default is case sensitive. If you want to be case-insensitive, you may need to use ILIKE (PostgreSQL support), or convert uniformly before querying:

   SELECT * FROM users WHERE LOWER(name) LIKE '%john%';

• Don't rely too much on LIKE to complete complex text searches

  For complex full-text search tasks, it is recommended to use a dedicated full-text search engine (such as Elasticsearch) or a built-in full-text search function instead of relying solely on LIKE .

4. Tips in practical applications

Sometimes we use LIKE and other conditions, such as NOT to eliminate certain situations:

 SELECT * FROM logs WHERE message NOT LIKE '%error%';

You can also cooperate with CASE WHEN for classification processing:

 SELECT 
  name,
  CASE 
    WHEN name LIKE 'A%' THEN 'Group'
    WHEN name LIKE 'B%' THEN 'Group B'
    ELSE 'Other'
  END AS group_name
FROM users;

This method is often used for report generation or data grouping.

Basically that's it. Mastering the usage of LIKE and wildcards can make you more flexible when processing string-like data.

The above is the detailed content of Using Wildcard Characters with the LIKE Operator in SQL.. For more information, please follow other related articles on the PHP Chinese website!