SQL Programming

SQL Wildcards
A Wildcard character can be used to substitute for any other character(s) in a string.
Note: In SQL, wildcard characters are used with the SQL LIKE operator.
With SQL, the wildcards are:
Wildcard | Description |
% | A substitute for zero or more characters |
_ | A substitute for a single character |
[charlist] | Set and ranges of characters to match |
[^charlist] or [!charlist] | Matches only a character NO |
SQL % Wildcard:
Example:
SELECT * FROM Customers WHERE City LIKE 'anu%';
Note: SQL statement select all customers with a City starting with “anu”.
Output:
CustomerId | CustomerName | ContactName | Address | City | PostalCode | Country |
7 | Anurag | Rakesh | 263 varma chauraha | Kanpur | 213101 | India |
Example:
SELECT * FROM Customers WHERE City LIKE '%pur%';
Note: SQL statement select all customers with a City containing the pattern “pur”.
Output:
CustomerId | CustomerName | ContactName | Address | City | PostalCode | Country |
1 | Mahendra | Null | Null | lakhimpur | 212501 | India |
2 | Abhishek | Dev | 23 lal chauk | Jaunpur | 212601 | India |
3 | Amit | Mahek | 453 Pakka | Fatehpur | 212701 | India |
4 | Divyanshu | Rajesh | 234 pani | Fatehpur | 212801 | India |
7 | Anurag | Rakesh | 263 varma chauraha | Kanpur | 213101 | India |
SQL _ Wildcard:
Example:
SELECT * FROM Customers WHERE City LIKE '_aunpur';
Note: SQL statement select all customers with a City starting with any character, followed by “aunpur”.
Output:
CustomerId | CustomerName | ContactName | Address | City | PostalCode | Country |
2 | Abhishek | Dev | 23 lal chauk | Jaunpur | 212601 | India |
SQL [charlist] Wildcard:
Example:
SELECT * FROM Customers WHERE City LIKE '[jap]%';
Note: SQL statement select all customers with a City starting with “j”, “a”, or “p”.
Output:
CustomerId | CustomerName | ContactName | Address | City | PostalCode | Country |
2 | Abhishek | Dev | 23 lal chauk | Jaunpur | 212601 | India |
5 | Himanshu | Abhilash | 123 civil line | Allahabad | 212901 | India |
6 | Shahwaz | Anshuman | 534 chaufatka | Patna | 213001 | India |
Example:
SELECT * FROM Customers WHERE City LIKE '[j-l]%';
Note: SQL statement select all customers with a City starting with “j”, “k”, or “l”.
Output:
CustomerId | CustomerName | ContactName | Address | City | PostalCode | Country |
2 | Abhishek | Dev | 23 lal chauk | Jaunpur | 212601 | India |
7 | Anurag | Rakesh | 263 varma chauraha | Kanpur | 213101 | India |
1 | Mahendra | Null | Null | lakhimpur | 212501 | India |
Example:
SELECT * FROM Customers WHERE City LIKE '[!jap]%';
OR
SELECT * FROM Customers WHERE City NOT LIKE '[jap]%';
Note: SQL statement select all customers with a City NOT starting with “j”, “a”, or “p”.
Output:
CustomerId | CustomerName | ContactName | Address | City | PostalCode | Country |
1 | Mahendra | Null | Null | lakhimpur | 212501 | India |
3 | Amit | Mahek | 453 Pakka | Fatehpur | 212701 | India |
4 | Divyanshu | Rajesh | 234 pani | Fatehpur | 212801 | India |
7 | Anurag | Rakesh | 263 varma chauraha | Kanpur | 213101 | India |
Learn how to use SQL to store, query, and manipulate data. SQL is a special-purpose programming language designed for managing data in a relational database, and is used by a huge number of apps and organizations.
SQL basics
Course Features
- Lectures 41
- Quizzes 0
- Duration 50 hours
- Skill level All levels
- Language English
- Students 0
- Certificate No
- Assessments Self