SQL [] Wildcard

What is the SQL [] Wildcard?

The SQL [] wildcard, often referred to as a character range or character set, is used within the LIKE operator to match a single character against a specified set of characters. It allows you to perform pattern matching where a character in a specific position within a column value can be any character from a defined range or set. This wildcard is useful for creating flexible and precise character-based searches in SQL.

When you would use it

You would use the SQL [] wildcard when you need to search for rows in a database where a character at a specific position within a column value matches a set of characters from a defined range. It is particularly useful when you want to perform character-based pattern matching and need to specify a range of acceptable characters.

Syntax

The [] wildcard is used within the LIKE operator. The syntax is as follows:

WHERE column_name LIKE pattern

• column_name: The name of the column you want to search in.
• pattern: The search pattern that includes [...] to represent a character range.

Parameter values

• column_name: The name of the column in which you want to search.
• pattern: The pattern you want to match against, where [...] represents a character range.

Example query

Suppose you have a table named "products" with a column "product_code," and you want to find products whose codes start with any uppercase letter from A to F. You can use the [A-F] character range with the LIKE operator as follows:

SELECT product_code
FROM products
WHERE product_code LIKE '[A-F]%';

In this query, [A-F] represents any uppercase letter from A to F, followed by % to match any characters after the initial letter.

Example table response

Assuming the "products" table contains the following data:

| product_code  |
| ------------  |
| A123          |
| B456          |
| G789          |
| C123          |
| D987          |
| F654          |
| H321          |

The query mentioned earlier would return the following result:

| product_code  |
| ------------  |
| A123          |
| B456          |
| C123          |
| D987          |
| F654          |

This result includes products with codes that start with uppercase letters from A to F.

Use cases

• Performing character-based searches within text or string columns with defined character ranges.
• Finding rows where a specific character sequence occurs at a particular position within a set range.
• Creating precise text pattern matching conditions.

SQL languages this is available for

The [] wildcard with the LIKE operator is a feature available in most relational database management systems (RDBMS). It can be used in SQL systems such as MySQL, PostgreSQL, Oracle Database, SQL Server, SQLite, and others. The syntax and behavior are generally consistent across these systems, making it a versatile tool for character-based pattern matching in SQL.