Previous

SQL LIKE

The LIKE operator in SQL is used to search for a specified pattern in a column. It is often used with wildcards to match values that meet certain criteria.

 

Basic Syntax of LIKE Operator

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern';
  • column_name: The column to search.
  • pattern: The text pattern to match, using wildcards.

 

Wildcards in LIKE

  1. %: Represents zero, one, or multiple characters.
  2. _: Represents a single character.

 

Key Features of LIKE

1. Match Values Starting with Specific Characters

SELECT name  
FROM employees  
WHERE name LIKE 'A%';
  • Retrieves all employee names starting with A.

2. Match Values Ending with Specific Characters

SELECT product_name  
FROM products  
WHERE product_name LIKE '%X';
  • Retrieves product names ending with X.

3. Match Values Containing Specific Characters

SELECT customer_name  
FROM customers  
WHERE customer_name LIKE '%John%';
  • Retrieves customer names containing John anywhere in the text.

4. Match Values with a Single Character

Use _ to replace a single character.

SELECT name  
FROM employees  
WHERE name LIKE 'J_n';
  • Retrieves names like Jon, Jan, or Jen where the second character is n.

5. Combine Wildcards

You can combine % and _ for complex patterns.

SELECT product_name  
FROM products  
WHERE product_name LIKE 'A__%';
  • Retrieves product names starting with A and having at least 3 characters.

6. NOT LIKE

Use NOT LIKE to exclude patterns.

SELECT name  
FROM employees  
WHERE name NOT LIKE 'A%';
  • Retrieves all employee names that do not start with A.

 

Example Queries

1. Match Names Starting with 'S'

SELECT name  
FROM employees  
WHERE name LIKE 'S%';
  • Retrieves names starting with S.

2. Match Cities Ending with 'o'

SELECT city  
FROM customers  
WHERE city LIKE '%o';
  • Retrieves cities ending with o.

3. Match Emails Containing 'gmail'

SELECT email  
FROM users  
WHERE email LIKE '%gmail%';
  • Retrieves emails that contain gmail.

4. Match Product Codes with Single Character Variations

SELECT product_code  
FROM products  
WHERE product_code LIKE 'P_1';
  • Retrieves product codes like P01, P11, or P21.

5. Exclude Names Containing 'Test'

SELECT name  
FROM users  
WHERE name NOT LIKE '%Test%';
  • Retrieves all names that do not contain the word Test.

 

Summary

The LIKE operator is a powerful tool for pattern matching in SQL. By using wildcards like % and _, you can search for flexible patterns within column values. Use NOT LIKE to exclude specific patterns.

Previous