SQL Wildcard
A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
Example:
SELECT column_Name1, column_Name2 …., column_NameN FROM table_Name WHERE column_name LIKE pattern:
Let’s take the following Employee table:
Return all customers starting with either “b”, “s”, or “p”:
SELECT * FROM Employee WHERE Name LIKE ‘[rsv]%’;
The [] wildcard returns a result if any of the characters inside gets a match.
Output:
Let’s take the following Student table:
Return all customers starting with “a”, “b”, “c”, “d”, “e”, “f”, “g”, “h”, “i”, “j” or “k”:
SELECT * FROM Name WHERE Name LIKE ‘[a-k]%’;
Output:
The – wildcard allows you to specify a range of characters inside the [] wildcard.
Course Video
NOTE: Practice below questions on site editor.
1. Write SQL query to get Firstname, Lastname, from Person_Person table where firstname starts with ‘b’.
Output:
2. Write SQL query to get addressline1, addressline2, city from person.address table where city start with ’M_ N’ .
Output:
3. Write SQL query to get productid, name, color, listprice from Production_Product table where productname start with ‘Adjustable Race’.
Output:
5. Write a query to fetch the city name start with ‘p’.
Table name:(Person_Address)
Output:
6. Find all departments whose Name contains the substring ‘ion’ and whose GroupName contains the substring ‘ing’ from HumanResources_Department table.
7. Write an SQL query to find all address types whose Name starts with the letter ‘S’ and the rowguid contains the digit ‘9’ at any position from Person_AddressType table.
8. Write an SQL query to find all BusinessEntityID entries where the rowguid starts with ‘3’, and ends with any letter (A-Z).
9. Write an SQL query to retrieve all records where the SalesYTD contains the substring ‘8’, does not start with ‘0’.
10. Write an SQL query to find all countries where the Name contains exactly three words. from Person_CountryRegion
YouTube Reference :
1) SQL Wildcard in Hindi/Urdu
2) SQL Wildcard in English
SQL wildcards are special characters used in the LIKE operator to substitute for one or more characters in a string. They are commonly used for pattern matching.
Wildcards work in combination with the LIKE operator to filter results based on patterns:
- %: Matches zero or more characters.
- _: Matches exactly one character.
For example:
- LIKE ‘J%’ finds names starting with “J”.
- Find names ending with “son”:
sqlCopy codeSELECT * FROM Employees WHERE name LIKE ‘%son’;
- Find names containing “oo”:
sqlCopy codeSELECT * FROM Employees WHERE name LIKE ‘%oo%’;
- Match a single character:
sqlCopy codeSELECT * FROM Employees WHERE name LIKE ‘J_n’;
- SQL Wildcards: Limited to the % and _ symbols for basic pattern matching.
- Regular Expressions: Offer more complex pattern-matching capabilities but are not natively supported by all SQL databases.
- Forgetting to enclose strings in single quotes.
- Using wildcards where exact matches are required, which can slow performance.
- Case sensitivity issues in certain databases like PostgreSQL.
Yes, wildcards can be used with numeric data treated as text. For example:
sqlCopy codeSELECT * FROM Orders WHERE order_id LIKE ‘12%’;
Yes, our website provides a detailed SQL Wildcards Tutorial with step-by-step examples.
Using % at the beginning of a pattern (e.g., %pattern) can slow down queries because the database must scan the entire dataset.
Yes, wildcards can be used with clauses like AND, OR, and NOT:
sqlCopy codeSELECT * FROM Products WHERE name LIKE ‘A%’ AND category = ‘Electronics’;
- Double-check the placement of % and _.
- Ensure patterns align with the data format.
- Use EXPLAIN to identify performance bottlenecks.
