The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Using LIKE for Pattern Matching

Next ►
►

Pattern matching is a powerful feature in SQL that allows you to search for data that matches a specific pattern. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Key Concepts

  1. LIKE Operator: Used to search for a specified pattern in a column.
  2. Wildcards: Special characters used with the LIKE operator to define patterns.
    • %: Represents zero or more characters.
    • _: Represents a single character.

Basic Syntax

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

Practical Examples

Example 1: Using % Wildcard

Suppose we have a table named employees with a column name. We want to find all employees whose names start with 'J'.

SELECT name
FROM employees
WHERE name LIKE 'J%';

Explanation:

  • J% matches any string that starts with 'J' followed by zero or more characters.

Example 2: Using _ Wildcard

Now, let's find all employees whose names have 'a' as the second character.

SELECT name
FROM employees
WHERE name LIKE '_a%';

Explanation:

  • _a% matches any string where the second character is 'a', and the first character can be anything.

Example 3: Combining Wildcards

Find all employees whose names start with 'J' and end with 'n'.

SELECT name
FROM employees
WHERE name LIKE 'J%n';

Explanation:

  • J%n matches any string that starts with 'J' and ends with 'n', with zero or more characters in between.

Practical Exercises

Exercise 1: Find Names Ending with 'son'

Task: Write a query to find all employees whose names end with 'son'.

SELECT name
FROM employees
WHERE name LIKE '%son';

Exercise 2: Find Names with 'a' as the Third Character

Task: Write a query to find all employees whose names have 'a' as the third character.

SELECT name
FROM employees
WHERE name LIKE '__a%';

Exercise 3: Find Names Containing 'an'

Task: Write a query to find all employees whose names contain 'an'.

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

Common Mistakes and Tips

  • Case Sensitivity: The LIKE operator is case-sensitive in some databases. Use ILIKE for case-insensitive searches if supported.
  • Escaping Wildcards: If you need to search for the actual % or _ characters, you must escape them using a backslash (\) or another escape character defined by your database.

Example: Escaping Wildcards

Find all employees whose names contain the literal string '50%'.

SELECT name
FROM employees
WHERE name LIKE '%50\%%' ESCAPE '\';

Explanation:

  • ESCAPE '\' tells SQL to treat the backslash as an escape character.

Conclusion

The LIKE operator is a versatile tool for pattern matching in SQL. By using wildcards % and _, you can create complex search patterns to filter your data effectively. Practice using these patterns to become proficient in data querying and manipulation.

In the next section, we will explore the IN and BETWEEN operators, which provide additional ways to filter data based on specific criteria.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project

The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved