SQL Wildcards

SQL wildcards are special characters that can be used to substitute for any other character(s) in a string.

They are used in SQL queries to search for specific patterns in data stored in a database.

Here is a list of the most common SQL wildcard characters:

  • % (percent sign) - matches any number of characters, including none at all
  • _ (underscore) - matches a single character
  • [] (square brackets) - matches any single character within a set of characters
  • [a-z] (range) - matches any single character within a range of characters
  • [^a-z] (negation) - matches any single character that is not within a range of characters
  • \ (escape character) - used to escape wildcard characters and treat them as literal characters

It's important to note that the specific wildcard characters and their usage may vary from one database management system to another, so it's good to check the documentation of the specific database management system you're using to ensure the correct wildcards for your queries.

Percent Sign (%)

The percent sign % is used to match any number of characters, including none at all.

For example, the following query will return all rows from the "customers" table where the "last_name" column starts with the letter "S":

SELECT * FROM customers
WHERE last_name LIKE 'S%';

In this example, the percent sign is used to match any number of characters that come after the letter "S" in the "last_name" column.

Underscore (_)

The underscore _ is used to match a single character, but unlike the percent sign, it does not match no character.

For example, the following query will return all rows from the "employees" table where the "employee_id" column is exactly four characters long and starts with the letter "E":

SELECT * FROM employees
WHERE employee_id LIKE 'E___';

In this example, the underscore is used to match a single character for each of the three positions that come after the letter "E" in the "employee_id" column.

Combining Wildcards

It is possible to use both percent sign and underscore wildcards in the same LIKE clause.

For example, the following query will return all rows from the "products" table where the "product_name" column starts with the letter "C" and is exactly five characters long:

SELECT * FROM products
WHERE product_name LIKE 'C___%';

In this example, the first underscore matches a single character, the second underscore matches a second single character, the third underscore matches a third single character, and the percent sign matches any number of characters that come after the five characters already matched.

Escape Character (\)

In some cases, you may want to search for a literal underscore or percent sign in a column, rather than using it as a wildcard.

In these cases, you can use the escape character, which is the backslash \ to escape the wildcard characters.

For example, the following query will return all rows from the "orders" table where the "comments" column contains the string "%discount":

SELECT * FROM orders
WHERE comments LIKE '%\%discount%' ESCAPE '\';

In this example, the backslash is used to escape the percent sign, so that it is treated as a literal character rather than a wildcard.

There are a few additional wildcards that can be used in SQL beyond the percent sign %, the underscore _ and the escape character \:

Square Brackets []

Square brackets [] can be used to match any single character within a set of characters.

For example, the following query will return all rows from the "employees" table where the "last_name" column starts with the letters "S" or "T":

SELECT * FROM employees
WHERE last_name LIKE '[ST]%';

In this example, the square brackets are used to match any single character that is either "S" or "T" at the start of the "last_name" column.

Ranges [A-Z]%

Ranges can be used to match any single character within a range of characters.

For example, the following query will return all rows from the "employees" table where the "employee_id" column starts with a letter between "A" and "C":

SELECT * FROM employees
WHERE employee_id LIKE '[A-C]%';

In this example, the range is used to match any single character that is between "A" and "C" at the start of the "employee_id" column.

Conclusion

SQL wildcards are a powerful tool for searching for specific patterns in data stored in a database.

They can be used in combination with the LIKE clause to search for specific characters or patterns in a column.

Wildcards can be used to match any number of characters or a single character and can be combined to match more complex patterns.

Understanding how to use wildcards in SQL can make your queries more powerful and efficient.

SQL Basics