-
Hajipur, Bihar, 844101
MySQL
Introduction to MySQL
MySQL SQL Basics
-
MySQL SQL
-
MySQL SELECT
-
MySQL WHERE
-
MySQL AND, OR, NOT
-
MySQL ORDER BY
-
MySQL INSERT INTO
-
MySQL NULL Values
-
MySQL UPDATE
-
MySQL DELETE
-
MySQL LIMIT
MySQL Functions & Operators
-
MySQL MIN and MAX
-
MySQL COUNT, AVG, SUM
-
MySQL LIKE
-
MySQL Wildcards
-
MySQL IN
-
MySQL BETWEEN
-
MySQL Aliases
-
MySQL CASE
-
MySQL EXISTS
-
MySQL ANY, ALL
-
MySQL Null Functions
-
MySQL Comments
-
MySQL Operators
MySQL Joins & Unions
-
MySQL Joins
-
MySQL INNER JOIN
-
MySQL LEFT JOIN
-
MySQL RIGHT JOIN
-
MySQL CROSS JOIN
-
MySQL Self Join
-
MySQL UNION
MySQL Grouping & Filtering
Database & Table Management
MySQL Constraints
-
MySQL Constraints
-
MySQL Not Null
-
MySQL Unique
-
MySQL Primary Key
-
MySQL Foreign Key
-
MySQL Check
-
MySQL Default
-
MySQL Create Index
-
MySQL Auto Increment
Date & Views
References
MySQL Wildcards
In MySQL, wildcards are special symbols used with the LIKE operator to perform pattern matching in string searches. They allow you to search for rows based on partial matches rather than exact text. Wildcards are widely used in applications that require flexible search capabilities, such as filtering user inputs, generating reports, and implementing search functionality on websites.
MySQL primarily uses two wildcard characters:
-
Percent
%– matches zero or more characters. -
Underscore
_– matches exactly one character.
Mastering wildcards allows you to perform complex text searches efficiently and with flexibility.
The Percent % Wildcard
The % wildcard matches any number of characters, including zero characters.
Usage Examples
-
Matches names starting with a letter:
SELECT name FROM students
WHERE name LIKE 'A%';
-
Returns all students whose names start with 'A', such as Aditi and Ananya.
-
Matches names ending with a letter:
SELECT name FROM students
WHERE name LIKE '%a';
-
Returns students whose names end with 'a', such as Meera, Riya, or Pooja.
-
Matches names containing a substring:
SELECT name FROM students
WHERE name LIKE '%an%';
-
Returns students whose names contain 'an', such as Ananya.
Practical Scenario
Suppose you have a table of products, and you want to find all products containing the word “Pro”:
SELECT product_name FROM products
WHERE product_name LIKE '%Pro%';
-
This query will return results like “MacBook Pro” and “GoPro Camera”.
The Underscore _ Wildcard
The _ wildcard matches exactly one character. It is useful when you know the length of a pattern, but not the exact characters.
Usage Examples
-
Match names with four characters:
SELECT name FROM students
WHERE name LIKE '____';
-
Returns all students whose names are exactly 4 letters long, such as Riya.
-
Match specific character positions:
SELECT name FROM students
WHERE name LIKE 'A__i';
-
Matches names like Anvi or Aditi, where the first letter is 'A' and the last is 'i'.
Practical Scenario
Suppose you want to find employee codes that follow the pattern E__1:
SELECT emp_code FROM employees
WHERE emp_code LIKE 'E__1';
-
Returns codes like EAB1 or ECD1, where the middle characters vary.
Combining % and _
You can combine % and _ for more complex searches:
SELECT name FROM students
WHERE name LIKE 'A__%';
-
Matches names starting with 'A', followed by exactly two characters, and any number of characters after that.
-
Returns names like Aditi or Ananya.
Using Wildcards with NOT
Wildcards can also be combined with NOT LIKE to exclude specific patterns:
SELECT name FROM students
WHERE name NOT LIKE 'A%';
-
Returns all students whose names do not start with 'A'.
Scenario
Exclude products starting with “Pro”:
SELECT product_name FROM products
WHERE product_name NOT LIKE 'Pro%';
-
Returns all products that do not begin with "Pro".
Wildcards and Case Sensitivity
-
By default, MySQL LIKE and wildcards are case-insensitive with the default collation.
-
To perform case-sensitive searches, use the
BINARYkeyword:
SELECT name FROM students
WHERE BINARY name LIKE 'a%';
-
Returns only names starting with lowercase “a”.
Scenario
Searching for usernames that start with a lowercase letter:
SELECT username FROM users
WHERE BINARY username LIKE 'u%';
-
Returns usernames like
user123but notUser123.
Wildcards with ORDER BY and GROUP BY
Wildcards can be combined with ORDER BY and GROUP BY for sorted and aggregated results:
SELECT name, city FROM students
WHERE name LIKE '%a%'
ORDER BY name ASC;
-
Returns names containing “a” sorted alphabetically.
SELECT city, COUNT(*) AS total_students
FROM students
WHERE name LIKE 'A%'
GROUP BY city;
-
Returns the number of students in each city whose names start with 'A'.
Performance Considerations
-
Queries using
%at the beginning of a pattern (e.g.,%an%) cannot use indexes efficiently, which can slow down large datasets. -
Queries using wildcards without leading
%(e.g.,A%) can use indexes and are much faster. -
For very large datasets, consider full-text search or external search engines for complex pattern matching.
Real-Life Examples
Suppose the students table contains:
| id | name | city |
|---|---|---|
| 1 | Aditi | Mumbai |
| 2 | Meera | Pune |
| 3 | Pooja | Pune |
| 4 | Riya | Delhi |
| 5 | Ananya | Mumbai |
Example 1 – Names starting with A:
SELECT name FROM students
WHERE name LIKE 'A%';
-
Returns: Aditi, Ananya
Example 2 – Names ending with a:
SELECT name FROM students
WHERE name LIKE '%a';
-
Returns: Meera, Pooja, Riya, Ananya
Example 3 – Names exactly 4 letters:
SELECT name FROM students
WHERE name LIKE '____';
-
Returns: Riya
Example 4 – Names not starting with A:
SELECT name FROM students
WHERE name NOT LIKE 'A%';
-
Returns: Meera, Pooja, Riya
Example 5 – Names containing specific pattern:
SELECT name FROM students
WHERE name LIKE '%an%';
-
Returns: Ananya
Example 6 – Complex pattern using % and _:
SELECT name FROM students
WHERE name LIKE 'A__%';
-
Returns names like Aditi or Ananya, starting with 'A', followed by exactly two characters, then any number of characters.
Summary of the Tutorial
-
Wildcards
%and_are used with LIKE for flexible pattern matching. -
%matches zero or more characters,_matches exactly one character. -
Use wildcards with WHERE, NOT LIKE, OR, ORDER BY, GROUP BY, and BINARY for advanced queries.
-
Proper use of wildcards enables text filtering, search functionality, and pattern-based data analysis.
-
Performance considerations are important when using leading
%, especially on large tables.
Practice Questions
-
Retrieve all students whose names start with 'A'.
-
Retrieve all students whose names end with 'a'.
-
Retrieve all students whose names contain 'ee' anywhere.
-
Retrieve all students whose names are exactly 4 letters long.
-
Retrieve students whose names do not start with 'P'.
-
Retrieve students whose names start with 'A' and have exactly 5 letters.
-
Retrieve students whose names contain 'an' but do not start with 'A'.
-
Retrieve students whose names start with a capital letter 'R' (case-sensitive).
-
Retrieve students whose names start with 'P', followed by any two letters, and end with 'a'.
-
Count the number of students whose names contain the substring 'oo'.
Try a Short Quiz.
No quizzes available.
MySQL
Introduction to MySQL
MySQL SQL Basics
-
MySQL SQL
-
MySQL SELECT
-
MySQL WHERE
-
MySQL AND, OR, NOT
-
MySQL ORDER BY
-
MySQL INSERT INTO
-
MySQL NULL Values
-
MySQL UPDATE
-
MySQL DELETE
-
MySQL LIMIT
MySQL Functions & Operators
-
MySQL MIN and MAX
-
MySQL COUNT, AVG, SUM
-
MySQL LIKE
-
MySQL Wildcards
-
MySQL IN
-
MySQL BETWEEN
-
MySQL Aliases
-
MySQL CASE
-
MySQL EXISTS
-
MySQL ANY, ALL
-
MySQL Null Functions
-
MySQL Comments
-
MySQL Operators
MySQL Joins & Unions
-
MySQL Joins
-
MySQL INNER JOIN
-
MySQL LEFT JOIN
-
MySQL RIGHT JOIN
-
MySQL CROSS JOIN
-
MySQL Self Join
-
MySQL UNION
MySQL Grouping & Filtering
Database & Table Management
MySQL Constraints
-
MySQL Constraints
-
MySQL Not Null
-
MySQL Unique
-
MySQL Primary Key
-
MySQL Foreign Key
-
MySQL Check
-
MySQL Default
-
MySQL Create Index
-
MySQL Auto Increment
Date & Views
References
