Finding Products with Similar Names

Many e-commerce platforms and databases need to search for products based on partial or fuzzy matches of their names. The LIKE operator in SQL provides a powerful way to achieve this, allowing you to find records where a column's value resembles a given pattern. This challenge will test your ability to use LIKE effectively to retrieve data based on name patterns.

Problem Description

You are tasked with writing a SQL query that retrieves all products from a Products table whose names contain a specific pattern. The pattern will be provided as input, and you need to construct a LIKE clause to match products whose names contain that pattern anywhere within the name. The query should be case-insensitive.

Key Requirements:

• The query must use the LIKE operator for pattern matching.
• The query must be case-insensitive (e.g., "apple" should match "Apple").
• The query should return all columns from the Products table.
• The query should handle cases where the pattern is an empty string (in which case, all products should be returned).
• The query should handle cases where the pattern contains wildcard characters (% and _).

Expected Behavior:

Given a Products table and a search pattern, the query should return all rows from the Products table where the ProductName column contains the search pattern, ignoring case. If the pattern is empty, all rows should be returned.

Edge Cases to Consider:

• Empty search pattern.
• Search pattern containing only wildcard characters.
• ProductName column containing NULL values (NULL values should not be matched).
• Large datasets (performance considerations, although not a primary focus for this challenge).

Examples

Example 1:

Input:
Products Table:
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Apple iPhone 13 | 999 |
| 2 | Samsung Galaxy S22 | 899 |
| 3 | Apple iPad Air | 599 |
| 4 | Google Pixel 6 | 799 |

Search Pattern: "apple"

Output:
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Apple iPhone 13 | 999 |
| 3 | Apple iPad Air | 599 |

Explanation: The query returns the products whose names contain "apple" (case-insensitive).

Example 2:

Input:
Products Table:
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Apple iPhone 13 | 999 |
| 2 | Samsung Galaxy S22 | 899 |
| 3 | Apple iPad Air | 599 |
| 4 | Google Pixel 6 | 799 |

Search Pattern: "%phone%"

Output:
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Apple iPhone 13 | 999 |

Explanation: The query returns the product whose name contains "phone" anywhere within the name. The '%' wildcard matches any sequence of characters.

Example 3:

Input:
Products Table:
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Apple iPhone 13 | 999 |
| 2 | Samsung Galaxy S22 | 899 |
| 3 | Apple iPad Air | 599 |
| 4 | Google Pixel 6 | 799 |

Search Pattern: "" (empty string)

Output:
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Apple iPhone 13 | 999 |
| 2 | Samsung Galaxy S22 | 899 |
| 3 | Apple iPad Air | 599 |
| 4 | Google Pixel 6 | 799 |

Explanation: The query returns all products because the search pattern is empty.

Constraints

• The Products table will always exist and contain at least the columns ProductID, ProductName, and Price.
• ProductName is a string type.
• The search pattern will be a string type.
• The database system is case-insensitive by default for LIKE comparisons.
• The maximum length of the ProductName is 255 characters.
• The maximum length of the search pattern is 50 characters.

Notes

• Consider using the LOWER() function to ensure case-insensitive matching if your database system is case-sensitive by default for LIKE.

• The % wildcard matches zero or more characters.

• The _ wildcard matches exactly one character.

• Focus on constructing the correct LIKE clause. The specific database syntax (e.g., MySQL, PostgreSQL, SQL Server) is not critical, but the core logic of using LIKE should be correct.

• Pseudocode:

  SELECT *
  FROM Products
  WHERE ProductName LIKE '%[search_pattern]%'
  

  (Remember to handle the empty string case appropriately, potentially with a conditional check.)