SQL Wildcards
SQL wildcards are used with the SQL LIKE operator to search for data within a table. Wildcards are characters that allow you to perform pattern matching within strings.
Basic Wildcards
There are two main wildcards used in SQL:
%– Represents zero, one, or multiple characters_– Represents a single character
Examples
Example 1: Using the % Wildcard
SELECT * FROM employees
WHERE first_name LIKE 'J%';This query selects all employees whose first names start with the letter ‘J’.
Example 2: Using the % Wildcard in the Middle
SELECT * FROM employees
WHERE first_name LIKE '%na%';This query selects all employees whose first names contain the substring ‘na’.
Example 3: Using the % Wildcard at the End
SELECT * FROM employees
WHERE last_name LIKE '%son';This query selects all employees whose last names end with ‘son’.
Example 4: Using the _ Wildcard
SELECT * FROM employees
WHERE first_name LIKE 'J_n';This query selects all employees whose first names are three letters long, start with ‘J’ and end with ‘n’.
Example 5: Combining % and _ Wildcards
SELECT * FROM products
WHERE product_code LIKE '12_%_202%';This query selects all products whose product codes start with ’12’, followed by any single character, followed by any character, followed by ‘202’, and ending with any character or characters.
Advanced Wildcard Usage
SQL also supports other wildcards, particularly in specific database systems like Microsoft Access:
[charlist]– Matches any single character in the list[^charlist]or[!charlist]– Matches any single character not in the list
Example 6: Using [charlist]
SELECT * FROM employees
WHERE first_name LIKE '[AB]%';This query selects all employees whose first names start with ‘A’ or ‘B’. (Note: This syntax might differ across SQL databases.)
Example 7: Using [^charlist] or [!charlist]
SELECT * FROM employees
WHERE first_name LIKE '[^AB]%';This query selects all employees whose first names do not start with ‘A’ or ‘B’. (Note: This syntax might differ across SQL databases.)
Using Wildcards with NOT LIKE
The LIKE operator can be combined with NOT to exclude patterns.
SELECT * FROM employees
WHERE first_name NOT LIKE 'J%';This query selects all employees whose first names do not start with ‘J’.
Case Sensitivity
The case sensitivity of the LIKE operator and wildcards depends on the database system. For example, in SQL Server, the LIKE operator is case-insensitive by default, while in MySQL, it is case-sensitive unless the column uses a case-insensitive collation.
Using SQL wildcards, you can perform complex pattern matching within your database queries, making it easier to find and manipulate data that fits specific patterns.