How to use like statement in sql

The LIKE statement is used to match characters or strings based on patterns in SQL. The syntax is: SELECT column_name FROM table_name WHERE column_name LIKE 'pattern'. It uses % to match zero or more characters, _ to match a single character, [ ] to match characters within square brackets, and ^ to match any character except the specified character. The default is case-sensitive, you can use the COLLATE clause for case-insensitive matching.

Usage of LIKE statement in SQL

Function of LIKE statement

The LIKE statement is used to match characters or strings based on patterns in SQL queries. It allows you to find values ​​that contain a specific sequence of characters or match a specific pattern.

Syntax

<code class="sql">SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';</code>

Pattern matching characters

The LIKE statement uses the following special characters as pattern matching characters:

• %: Matches zero or more characters.
• _: Matches any single character.
• [ ]: Matches any character within square brackets.
• ¹: Matches any character within square brackets except the specified characters.

Example

Find words starting with "A":

<code class="sql">SELECT word
FROM dictionary
WHERE word LIKE 'A%';</code>

Find words containing " ing" words:

<code class="sql">SELECT word
FROM dictionary
WHERE word LIKE '%ing';</code>

Find words that do not end with "x":

<code class="sql">SELECT word
FROM dictionary
WHERE word NOT LIKE '%x';</code>

Find words that contain numbers:

<code class="sql">SELECT word
FROM dictionary
WHERE word LIKE '%[0-9]%';</code>

Case sensitivity

By default, the LIKE statement is case-sensitive. To perform a case-insensitive match, use the COLLATE clause.

Example: Case-insensitive matching

<code class="sql">SELECT word
FROM dictionary
WHERE word COLLATE NOCASE LIKE 'APPLE';</code>

Note:

• The LIKE statement uses pattern matching, rather than an exact match.
• The matching characters % and _ must be escaped to match themselves.
• For performance optimization, use % at the beginning and end of the LIKE pattern.

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