How Do Special Characters Work in SQL's LIKE Clause Across Different Database Systems?

Complete List of Special Characters for SQL LIKE Clause

Introduction

The LIKE clause in SQL allows you to search for patterns within string data. This article provides a comprehensive list of all special characters used in the LIKE clause, specifically for SQL Server and some other popular database systems.

SQL Server

• %: Matches any string of zero or more characters.
• _: Matches any single character.
• [specifier]: Matches any single character within the specified range (e.g., [a-z]).
• ¹: Matches any single character not within the specified range.
• ESCAPE clause: Used to specify a character that acts as an escape character, allowing you to match special characters literally (e.g., 0!%%' ESCAPE '!' will evaluate 30% as true).
• ' characters: Need to be escaped with ' (e.g., they're becomes they''re).

MySQL

• %: Any string of zero or more characters.
• _: Any single character.
• ESCAPE clause: Similar to SQL Server.

Oracle

• %: Any string of zero or more characters.
• _: Any single character.
• ESCAPE clause: Similar to SQL Server.

Sybase

• %
• _
• [specifier]
• ¹

Progress

• %: Any string of zero or more characters.
• _: Any single character.

PostgreSQL

• %: Any string of zero or more characters.
• _: Any single character.
• ESCAPE clause: Similar to SQL Server.

ANSI SQL92

• %
• _
• An ESCAPE character (only if specified).

Additional PostgreSQL Specific Characters

• [specifier]
• ¹
• |: Either of two alternatives.
• *: Repetition of the previous item zero or more times.
• : Repetition of the previous item one or more times.
• (): Group items together.

---

1. specifier ↩

The above is the detailed content of How Do Special Characters Work in SQL's LIKE Clause Across Different Database Systems?. For more information, please follow other related articles on the PHP Chinese website!