SQL Pattern Matching: Finding Customers by Name Fragment

This challenge will test your ability to use the LIKE operator in SQL for flexible pattern matching. You'll be tasked with retrieving customer records based on partial matches within their names, a common requirement for searching and filtering data.

Problem Description

You are given a Customers table with information about various customers. Your goal is to write a SQL query that retrieves all customers whose FirstName starts with a specific prefix.

Key Requirements:

• Query the Customers table.
• Filter records based on the FirstName column.
• Use the LIKE operator to match names that begin with a given character sequence.

Expected Behavior: The query should return all rows from the Customers table where the FirstName column begins with the provided prefix.

Important Considerations:

• Case sensitivity: Assume the LIKE operator is case-insensitive by default in the target SQL environment, unless otherwise specified by the database system. For this challenge, assume case-insensitive matching.
• Empty prefix: If an empty string is provided as the prefix, all customers should be returned.

Examples

Example 1:

Input Table: Customers
+------------+-----------+----------+
| CustomerID | FirstName | LastName |
+------------+-----------+----------+
| 1          | Alice     | Smith    |
| 2          | Bob       | Johnson  |
| 3          | Alicia    | Williams |
| 4          | Charles   | Brown    |
+------------+-----------+----------+

Prefix to match: "Al"

Output Table:
+------------+-----------+----------+
| CustomerID | FirstName | LastName |
+------------+-----------+----------+
| 1          | Alice     | Smith    |
| 3          | Alicia    | Williams |
Explanation: 'Alice' and 'Alicia' both start with "Al".

Example 2:

Input Table: Customers
+------------+-----------+----------+
| CustomerID | FirstName | LastName |
+------------+-----------+----------+
| 1          | Alice     | Smith    |
| 2          | Bob       | Johnson  |
| 3          | Alicia    | Williams |
| 4          | Charles   | Brown    |
+------------+-----------+----------+

Prefix to match: "B"

Output Table:
+------------+-----------+----------+
| CustomerID | FirstName | LastName |
+------------+-----------+----------+
| 2          | Bob       | Johnson  |
Explanation: Only 'Bob' starts with "B".

Example 3: (Edge Case)

Input Table: Customers
+------------+-----------+----------+
| CustomerID | FirstName | LastName |
+------------+-----------+----------+
| 1          | Alice     | Smith    |
| 2          | Bob       | Johnson  |
| 3          | Alicia    | Williams |
| 4          | Charles   | Brown    |
+------------+-----------+----------+

Prefix to match: "" (empty string)

Output Table:
+------------+-----------+----------+
| CustomerID | FirstName | LastName |
+------------+-----------+----------+
| 1          | Alice     | Smith    |
| 2          | Bob       | Johnson  |
| 3          | Alicia    | Williams |
| 4          | Charles   | Brown    |
Explanation: An empty prefix should match all records.

Constraints

• The Customers table will contain at least one row.
• The FirstName column will not contain NULL values.
• The prefix to match will be a string.

Notes

• Remember that the LIKE operator uses wildcard characters. The % wildcard typically represents zero or more characters.
• Consider how to construct your pattern string to achieve the "starts with" behavior.
• While this example focuses on "starts with," the LIKE operator can be used for more complex pattern matching (e.g., contains, ends with).