How to use like in sql

The LIKE operator is used for pattern matching and fuzzy search in SQL. You can search for matching rows by specifying a pattern. Patterns include wildcard characters such as % (matches any character), _ (matches a single character), [] (matches characters within square brackets), and [^] (matches characters without brackets). The LIKE operator supports prefix, suffix, inclusion, and exact matching, and uses the % wildcard character in fuzzy searches. Note that this operator is not case-sensitive and its performance depends on the complexity of the pattern.

LIKE operator in SQL

The LIKE operator is used for pattern matching and fuzzy search in SQL operator. It allows you to specify a pattern or template and then search for lines that match it.

Syntax

<code>SELECT 列名
FROM 表名
WHERE 列名 LIKE 模式</code>

Pattern composition

• %: Matches any number of any character.
• _: Matches a single arbitrary character.
• []: Matches any single character within square brackets.
• [^]: Matches any single character that does not contain characters within square brackets.

Usage

The LIKE operator is used for fuzzy search:

• Prefix search: Patterns suffixed with "%" will match values ​​starting with that pattern. For example, "LIKE 'j%'" will match "John", "James", etc.
• Suffix search: Patterns prefixed with "%" will match values ​​ending with that pattern. For example, "LIKE '%son'" will match "Johnson", "Nelson", etc.
• Inclusion search: Patterns using the "%" prefix and suffix will match values ​​that contain the pattern. For example, "LIKE '%on'" will match "John", "Johnson", "Nelson", etc.
• Exact match: To perform an exact match, you can use a pattern without wildcards. For example, "LIKE 'John'" will match only "John".

Example

<code>SELECT *
FROM customers
WHERE name LIKE 'Jo%'</code>

This query will return all customer names that begin with "Jo".

<code>SELECT *
FROM products
WHERE description LIKE '%computer%'</code>

This query will return all products that contain "computer" in their description.

<code>SELECT *
FROM orders
WHERE order_id LIKE '[a-z]%'</code>

This query will return all order IDs starting with a lowercase letter.

Note

• The LIKE operator is not case-sensitive.
• If there are no wildcard characters in the pattern, the LIKE operator will perform an exact match.
• The performance of the LIKE operator may vary depending on the complexity of the pattern.

The above is the detailed content of How to use like in sql. For more information, please follow other related articles on the PHP Chinese website!