E-commerce Product Search with String Functions

intermediate

E-commerce site needs case-insensitive product search and formatted display. Challenge: Direct string comparison is case-sensitive. Solution: Use UPPER()/LOWER() for search, CONCAT() for display.

-- Product search with string functions

-- Case-insensitive search (BAD: prevents index)

SELECT product_id, name, price

FROM products

WHERE UPPER(name) LIKE UPPER('%laptop%');

-- Sequential scan (slow)

-- Better: Use generated column with index

ALTER TABLE products

ADD COLUMN name_upper VARCHAR(200) AS (UPPER(name)) STORED;

CREATE INDEX idx_products_name_upper ON products(name_upper);

SELECT product_id, name, price

FROM products

WHERE name_upper LIKE '%LAPTOP%';

-- Index scan (faster)

-- Format product display

SELECT

product_id,

CONCAT(name, ' - ', category) AS display_name,

CONCAT('$', ROUND(price, 2)) AS formatted_price,

SUBSTRING(description, 1, 100) AS short_description

FROM products

WHERE name_upper LIKE '%LAPTOP%'

ORDER BY price

LIMIT 20;

-- Extract product code prefix

SELECT

product_id,

name,

SUBSTRING(product_code, 1, 3) AS category_code,

SUBSTRING(product_code, 5) AS item_number

FROM products

WHERE SUBSTRING(product_code, 1, 3) = 'LAP';

-- product_code='LAP-123' -> category_code='LAP', item_number='123'

String functions enable search and formatting. Use generated columns for case-insensitive search (faster with index). CONCAT() formats display. SUBSTRING() extracts codes. Real-world: Amazon uses generated columns for case-insensitive search.

Generated columns enable fast case-insensitive search. Functions in SELECT format display (applied after filtering). SUBSTRING() extracts codes. Use for search and display, avoid functions in WHERE on indexed columns.

MySQL

Generated columns: Enable indexing for functions. Functions in SELECT: Fine (applied after filtering). Functions in WHERE: Prevent indexes (slow). Real-world: Amazon uses generated columns for search.