Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL LIKE and Wildcards
The LIKE operator in SQL is used to search for a specified pattern in a column. It is often used with wildcard characters to match various patterns in string data.
Wildcard Characters
- Percent Sign (%): Represents zero or more characters.
- Underscore (_) : Represents a single character.
Basic Syntax
Syntax:
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;
1. Using Percent Sign (%)
The percent sign (%) is used to match zero or more characters.
Example 1:
Find Employees with Names Starting with ‘A’
SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE ‘A%’;
Output:
employee_id | first_name | last_name |
2 | Aarti | Mehta |
7 | Anil | Sharma |
12 | Amit | Kumar |
Example 2:
Find Products with Names Containing ‘Shoe’
SELECT product_id, product_name
FROM products
WHERE product_name LIKE ‘%Shoe%’;
Output:
product_id | product_name |
101 | Sports Shoe |
203 | Running Shoes |
305 | Formal Shoes |
Example 3:
Find Customers with Email Ending in ‘gmail.com’
SELECT customer_id, email
FROM customers
WHERE email LIKE ‘%gmail.com’;
Output:
customer_id | |
1 | rajesh@gmail.com |
3 | priya@gmail.com |
7 | deepak@gmail.com |
2. Using Underscore (_)
The underscore (_) is used to match a single character.
Example 1:
Find Employees with Names Having ‘a’ as the Second Character
SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE ‘_a%’;
Output:
employee_id | first_name | last_name |
3 | Rajesh | Singh |
8 | Aarti | Mehta |
Example 2:
Find Product Codes with Exactly 3 Characters Followed by ‘X’
SELECT product_code, product_name
FROM products
WHERE product_code LIKE ‘___X’;
Output:
product_code | product_name |
ABX | Widget A |
C1X | Gadget B |
Example 3:
Find Customers with Names Starting with ‘S’ and Ending with ‘a’
SELECT customer_id, first_name, last_name
FROM customers
WHERE first_name LIKE ‘S_a’;
Output:
customer_id | first_name | last_name |
2 | Sita | Patel |
5 | Samta | Joshi |
3. Combining Wildcards
You can combine the percent sign and underscore to create complex patterns.
Example 1:
Find Employees with Names Starting with ‘R’ and Ending in ‘i’
SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE ‘R%i’;
Output:
employee_id | first_name | last_name |
1 | Rajesh | Singh |
6 | Ravi | Patel |
Example 2:
Find Products with Names Having ‘Smart’ Followed by Any Characters
SELECT product_id, product_name
FROM products
WHERE product_name LIKE ‘Smart%’;
Output:
product_id | product_name |
102 | Smart Watch |
204 | Smart Phone |
Example 3:
Find Customers with 5 Letters in Their Last Name
SELECT customer_id, last_name
FROM customers
WHERE last_name LIKE ‘_____’ ; — Exactly 5 characters
Output:
customer_id | last_name |
4 | Sharma |
8 | Gupta |
Tips to Remember
- Use % for Multiple Characters: Use the percent sign (%) to match any number of characters, including zero.
- Use _ for Single Character: Use the underscore (_) to match exactly one character.
- Combine Wildcards: Combine % and _ to form more specific search patterns.
- Case Sensitivity: Be aware of case sensitivity in pattern matching, which can vary between SQL implementations.
Power of LIKE and Wildcards
The LIKE operator with wildcards:
- Facilitates Flexible Searches: Allows for flexible and broad searches in string data.
- Enables Pattern Matching: Helps in finding data that matches specific patterns or criteria.
- Improves Data Filtering: Useful in filtering data when exact matches are not possible or not desired.
Frequently Asked Questions
Q1: Can I use wildcards with numeric columns?
A1: No, wildcards like % and _ are used with string (text) columns. For numeric columns, you should use range queries or comparison operators.
Q2: How does LIKE differ from =?
A2: The = operator is used for exact matches, while LIKE allows for pattern matching with wildcards.
Q3: Can I use LIKE with JOIN operations?
A3: Yes, you can use LIKE in JOIN operations to filter records based on pattern matching.
Q4: How do I search for literal ‘%’ or ‘_’ characters?
A4: To search for literal ‘%’ or ‘_’ characters, escape them using square brackets or backslashes, depending on your SQL database.
Example (SQL Server):
SELECT product_name
FROM products
WHERE product_name LIKE ‘%[[]%[[]’;
Q5: Can I use LIKE in ORDER BY clauses?
A5: Yes, you can use LIKE in ORDER BY to sort results based on pattern matches.
