What are the wildcard characters that are used with the LIKE command?

The wildcard characters commonly used with the LIKE command in SQL are the percent sign (%) and the underscore (_). These characters allow for flexible pattern matching when searching for data within text fields.

Understanding SQL LIKE Wildcards

The LIKE operator is a logical operator in SQL that determines if a character string matches a specified pattern. This pattern can include regular characters and wildcard characters. The two primary wildcards serve distinct purposes in defining these patterns.

The Percent Sign (%)

The percent sign (%) is a versatile wildcard that represents zero, one, or multiple characters. It's often used when you need to match any sequence of characters of any length.

Function: Matches any sequence of characters (including an empty string).
Common Use Cases:
- Starts with: '%pattern' (e.g., WHERE ProductName LIKE 'Ch%' finds products starting with "Ch").
- Ends with: 'pattern%' (e.g., WHERE City LIKE '%burg' finds cities ending with "burg").
- Contains: '%pattern%' (e.g., WHERE Description LIKE '%waterproof%' finds descriptions containing "waterproof").
- Any length: LIKE 'A%' matches any string starting with 'A'.

Example:
To find all customers whose names start with the letter 'J':

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE 'J%';

The Underscore Sign (_)

The underscore sign (_) is a wildcard that represents exactly one single character. It's useful when you know the position of a character but not its exact value, or when you need to match patterns of a specific length.

Function: Matches exactly one single character.
Common Use Cases:
- Specific length: '___' matches any three-character string.
- Character at a position: '_o%' matches strings with 'o' as the second character.
- Fixed pattern with one unknown: 'H_t' matches "Hot", "Hat", "Hit", etc.

Example:
To find all products that have a name with 'o' as the second letter:

SELECT ProductName
FROM Products
WHERE ProductName LIKE '_o%';

Comparing % and _ Wildcards

Here's a quick reference table summarizing the two wildcards:

Wildcard	Description	Example Pattern	Matches	Does NOT Match
`%`	Represents zero, one, or multiple	`a%`	`a`, `apple`, `applications`	`banana`, `orange`
`_`	Represents exactly one single	`h_t`	`hat`, `hot`, `hit`	`heat`, `hunt`, `hate` (too many characters)

Practical Considerations and Escaping Wildcards

Combining Wildcards: You can combine both % and _ in a single LIKE pattern for more complex searches, such as LIKE 'A_%_G' which would match strings starting with 'A', having at least two more characters, and ending with 'G'.
Case Sensitivity: The case sensitivity of LIKE operations can vary depending on the specific SQL database system and its configuration (e.g., COLLATE settings). Some systems are case-sensitive by default (e.g., PostgreSQL for LIKE), while others are not (e.g., MySQL for LIKE).
Escaping Wildcards: If you need to search for the literal percent sign (%) or underscore (_) character within your data, you must use an ESCAPE clause. This allows you to define an escape character (e.g., \) which precedes the wildcard you want to treat as a literal.

Example: To find strings that contain 50%:
```
SELECT ItemDescription
FROM Products
WHERE ItemDescription LIKE '%50\%%' ESCAPE '\';
```
In this example, the backslash \ acts as the escape character, telling SQL to treat the following % as a literal character rather than a wildcard.