String Transformation and Analysis

This challenge focuses on the practical application of common string manipulation functions in SQL: LENGTH, UPPER, and LOWER. You'll work with a dataset of product names and descriptions, performing transformations and deriving insights based on their string properties. Understanding these functions is fundamental for data cleaning, standardization, and analysis in any database environment.

Problem Description

You are tasked with processing a table containing product information. For each product, you need to perform the following operations:

1. Calculate the length of the product's product_name.
2. Standardize the product_name by converting it to uppercase.
3. Standardize the product_description by converting it to lowercase.
4. Identify products where the original product_name (case-insensitive comparison) is "apple" followed by any characters. This means "Apple", "APPLE", "apple pie", "Apple Juice" should all match.

You will need to output a result set that includes the original product details along with the transformed data and a flag indicating if the product name matches the specific pattern.

Key Requirements:

• Use the LENGTH function to get the number of characters in product_name.
• Use the UPPER function to convert product_name to uppercase.
• Use the LOWER function to convert product_description to lowercase.
• Implement a condition to check if the product_name starts with "apple" (case-insensitively). You can achieve this by converting the product_name to lowercase before comparison.

Expected Behavior:

The output should contain rows for each product in the input table. Each row should include:

• product_id
• product_name (original)
• product_description (original)
• name_length (integer: length of product_name)
• uppercase_name (string: product_name in uppercase)
• lowercase_description (string: product_description in lowercase)
• is_apple_product (boolean/integer: TRUE or 1 if product_name starts with "apple" case-insensitively, FALSE or 0 otherwise).

Edge Cases:

• Empty product_name or product_description fields.
• Product names with leading/trailing spaces (these should be included in the length calculation unless explicitly handled by trimming, which is not required for this specific challenge).

Examples

Example 1:

Input Table: products

Output:

Explanation:

• Row 1: "Laptop" has length 6, becomes "LAPTOP" in uppercase. Description is lowercased. "Laptop" does not start with "apple".
• Row 2: "APPLE JUICE" has length 10, becomes "APPLE JUICE". Description is lowercased. "APPLE JUICE" (when lowercased to "apple juice") starts with "apple".
• Row 3: "Keyboard" has length 8, becomes "KEYBOARD". Description is lowercased. "Keyboard" does not start with "apple".

Example 2:

Input Table: products

Output:

Explanation:

• Row 4: "apple pie" has length 9, becomes "APPLE PIE". Description is lowercased. "apple pie" starts with "apple".
• Row 5: "Banana" has length 6, becomes "BANANA". Description is lowercased. "Banana" does not start with "apple".
• Row 6: An empty product_name has length 0, becomes an empty string. Description is lowercased. An empty string does not start with "apple".

Constraints

• product_id is a unique identifier.
• product_name and product_description are strings.
• The length of product_name will not exceed 255 characters.
• The length of product_description will not exceed 1000 characters.
• The solution should be a single SQL query.

Notes

• Pseudocode for string comparison: IF LOWER(product_name) LIKE 'apple%' THEN TRUE ELSE FALSE
• Consider how your specific SQL dialect handles boolean types for the is_apple_product column. Some might use 1/0, others TRUE/FALSE.
• The LENGTH function typically counts characters. Be mindful of potential differences if dealing with multi-byte character sets, though for this challenge, assume standard character counting.