• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL LIKE

← Previous Next →

In MySQL, the LIKE operator is used to search for a specific pattern within string columns. Unlike an exact match using = or <>, LIKE allows you to find rows that partially match a pattern. This makes it extremely useful for searching, filtering, and text analysis.

For example, you can use LIKE to find all students whose names start with "A", end with "a", or contain the letters "an". This flexibility is widely applied in web applications, reports, and user search features.

Basic Syntax

The basic syntax of LIKE is:

SELECT column1, column2
FROM table_name
WHERE column_name LIKE 'pattern';

  • column_name is the column you want to search.

  • pattern defines the string pattern you want to match.

  • The two main wildcards used with LIKE are % (percent) and _ (underscore).

Example:

SELECT name FROM students
WHERE name LIKE 'A%';

  • Returns all students whose names start with "A".

  • % represents zero or more characters.

Wildcards in LIKE

1. Percent Sign %

The % wildcard matches any number of characters, including zero characters.

Examples:

SELECT name FROM students
WHERE name LIKE 'A%';      -- Starts with 'A'

SELECT name FROM students
WHERE name LIKE '%a';      -- Ends with 'a'

SELECT name FROM students
WHERE name LIKE '%an%';    -- Contains 'an' anywhere

  • % can be placed at the start, end, or both ends of a pattern.

  • This allows for flexible partial matching.

2. Underscore _

The _ wildcard matches exactly one character.

Examples:

SELECT name FROM students
WHERE name LIKE '_iya';    -- Matches Riya, Diya, etc.

SELECT name FROM students
WHERE name LIKE 'A__i';    -- Matches names like Anvi, Aditi

  • Underscore is useful when the length is known, but not the exact letters.

Using LIKE with NOT

To exclude certain patterns, use NOT LIKE:

SELECT name FROM students
WHERE name NOT LIKE 'A%';

  • Returns students whose names do not start with "A".

  • Useful for filtering out unwanted results.

LIKE with Multiple Patterns

To search for multiple patterns at once, combine LIKE with OR:

SELECT name FROM students
WHERE name LIKE 'A%' OR name LIKE 'R%';

  • Returns students whose names start with "A" or "R".

  • Can be expanded for multiple combinations as needed.

LIKE and Case Sensitivity

  • By default, LIKE in MySQL is case-insensitive due to the default collation (utf8_general_ci).

  • For case-sensitive searches, use the BINARY keyword:

SELECT name FROM students
WHERE BINARY name LIKE 'a%';

  • Only matches names starting with lowercase “a”.

This is useful in scenarios where exact casing matters, such as codes, IDs, or passwords.

LIKE with ORDER BY

You can combine LIKE with ORDER BY to sort results:

SELECT name, city FROM students
WHERE name LIKE '%a%'
ORDER BY name ASC;

  • Returns students whose names contain “a”, sorted alphabetically.

  • Useful when showing search results on a webpage.

LIKE with GROUP BY

Although less common, LIKE can be used with GROUP BY to generate aggregated reports:

SELECT city, COUNT(*) AS total_students
FROM students
WHERE name LIKE 'A%'
GROUP BY city;

  • Counts students whose names start with “A” in each city.

  • Helps in category-wise text analysis.

Performance Considerations

  • LIKE queries with a leading % (e.g., %an%) cannot use indexes efficiently, which may slow down queries on large datasets.

  • LIKE queries without a leading % (e.g., A%) can use indexes, resulting in faster searches.

  • For large tables, consider full-text search or external search engines for complex pattern searches.

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 containing ‘an’:

SELECT name FROM students
WHERE name LIKE '%an%';

  • Returns: Ananya

Example 4 – Names exactly 4 letters:

SELECT name FROM students
WHERE name LIKE '____';

  • Returns: Riya

Example 5 – Names not starting with A:

SELECT name FROM students
WHERE name NOT LIKE 'A%';

  • Returns: Meera, Pooja, Riya

Example 6 – Names starting with A or R:

SELECT name FROM students
WHERE name LIKE 'A%' OR name LIKE 'R%';

  • Returns: Aditi, Ananya, Riya

Example 7 – Names containing multiple patterns (case-sensitive):

SELECT name FROM students
WHERE BINARY name LIKE 'A%' OR BINARY name LIKE 'R%';

  • Only matches names starting with uppercase “A” or “R”.

Summary of the Tutorial

  • The LIKE operator is essential for pattern-based searches.

  • % matches zero or more characters, while _ matches exactly one character.

  • LIKE can be combined with WHERE, NOT, OR, GROUP BY, ORDER BY, and BINARY for advanced queries.

  • Using LIKE efficiently allows flexible searches, filtering, and text analysis in MySQL.

  • Proper use of wildcards and awareness of performance considerations ensures fast and accurate query results, even with large datasets.

Practice Questions

  1. Retrieve all students whose names start with 'A'.

  2. Retrieve all students whose names end with 'a'.

  3. Retrieve all students whose names contain 'an' anywhere.

  4. Retrieve students whose names are exactly 4 characters long.

  5. Retrieve students whose names do not start with 'A'.

  6. Retrieve students whose names start with 'A' or 'R'.

  7. Retrieve students whose names start with lowercase 'a' (case-sensitive).

  8. Retrieve students whose names contain both 'e' and 'a' anywhere in the name.

  9. Retrieve students whose names start with 'P' and are exactly 4 letters long.

  10. Count the number of students whose names end with 'i'.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top