WikiGalaxy
SQL Wildcards
Introduction to SQL Wildcards
SQL Wildcards are special characters used in SQL queries to search for data within a database. They are used with the SQL LIKE operator to search for a specified pattern in a column.
SELECT * FROM Customers WHERE City LIKE 'A%';
This query selects all records from the Customers table where the City starts with the letter 'A'. The '%' wildcard represents zero or more characters.
Console Output:
Anna
Auckland
Using the Underscore Wildcard
The underscore (_) wildcard in SQL is used to match a single character in a string.
SELECT * FROM Products WHERE ProductName LIKE '_a%';
This query selects all products where the second letter of the product name is 'a'.
Console Output:
Bag
Fan
Combining Wildcards
You can combine wildcards in SQL to create complex search patterns.
SELECT * FROM Employees WHERE Name LIKE 'J_n%';
This query selects employees whose names start with 'J', have 'n' as the third character, followed by any sequence of characters.
Console Output:
John
Jane
Searching for Patterns in SQL
Wildcards are essential for searching specific patterns within SQL databases, allowing more flexible data retrieval.
SELECT * FROM Orders WHERE OrderID LIKE '%23%';
This query retrieves all orders where the OrderID contains the number '23' anywhere in the string.
Console Output:
12345
923
Using Wildcards with NOT LIKE
The NOT LIKE operator can be combined with wildcards to exclude certain patterns.
SELECT * FROM Customers WHERE City NOT LIKE '%ville';
This query selects customers whose city names do not end with 'ville'.
Console Output:
New York
Berlin
Wildcard Search for Numeric Patterns
Wildcards can also be used for numeric patterns if the column is stored as a string.
SELECT * FROM Products WHERE Code LIKE '12%';
This query selects products with codes starting with '12'.
Console Output:
1234
1289
Wildcard Usage in Different SQL Databases
Different SQL databases may have variations in wildcard usage, but the concept remains the same.
SELECT * FROM Books WHERE Title LIKE '%Adventure%';
This query retrieves all books with 'Adventure' in their title. This is a common pattern across different SQL dialects.
Console Output:
The Great Adventure
Adventure Time
Advanced Wildcard Pattern Matching
Advanced pattern matching can be achieved by combining multiple wildcards and patterns.
SELECT * FROM Employees WHERE Email LIKE '_%@company.com';
This query selects employees whose email addresses have exactly one character before the '@company.com' domain.
Console Output:
a@company.com
b@company.com
