-- % matches zero or more characters of any kind

-- Prefix match (starts with 'Mac')
SELECT * FROM customers WHERE last_name LIKE 'Mac%';

-- Suffix match (ends with '.org')
SELECT * FROM users WHERE email LIKE '%.org';

-- Substring match (contains 'mysql')
SELECT * FROM articles WHERE title LIKE '%mysql%';

-- Starts with A, ends with Z (anything in between)
SELECT * FROM products WHERE sku LIKE 'A%Z';

-- _ matches exactly ONE character

-- Exactly one character in the second position
SELECT * FROM products WHERE sku LIKE 'A_01';
-- Matches: AA01, AB01, AC01 -- does NOT match A01 or AAA01

-- Exactly 10 characters
SELECT * FROM contacts WHERE phone LIKE '__________';  -- 10 underscores

-- Mix: 5-character code starting with Z
SELECT * FROM codes WHERE code LIKE 'Z____';

-- At least one char after the dot
SELECT * FROM files WHERE filename LIKE '%.%_';

-- Default utf8mb4_unicode_ci collation: case-insensitive
SELECT * FROM users WHERE username LIKE 'alice%';
-- Matches: alice, Alice, ALICE, AlIcE

-- Force case-sensitive match with LIKE BINARY
SELECT * FROM users WHERE username LIKE BINARY 'alice%';
-- Matches: alice only

-- Alternative: use a binary collation in the comparison
SELECT * FROM users
WHERE username COLLATE utf8mb4_bin LIKE 'alice%';

-- Check the collation of a column
SHOW FULL COLUMNS FROM users LIKE 'username';

-- Exclude test accounts from a report
SELECT * FROM users WHERE email NOT LIKE '%@test.%';

-- Exclude multiple file extensions
SELECT * FROM uploads
WHERE filename NOT LIKE '%.tmp'
  AND filename NOT LIKE '%.bak';

-- Find customers whose names do NOT start with a vowel
SELECT * FROM customers
WHERE first_name NOT LIKE 'A%'
  AND first_name NOT LIKE 'E%'
  AND first_name NOT LIKE 'I%'
  AND first_name NOT LIKE 'O%'
  AND first_name NOT LIKE 'U%';

-- Default backslash escape: search for literal percent sign
SELECT * FROM discounts WHERE description LIKE '50\% off';

-- Search for literal underscore (e.g. column name 'user_id')
SELECT * FROM columns_log WHERE col_name LIKE 'user\_%';
-- Matches: user_id, user_name (the _ is literal, % matches the rest)

-- Custom escape character using ESCAPE
SELECT * FROM tags WHERE name LIKE '50!% off' ESCAPE '!';
-- ! is now the escape char; !% means a literal %

-- Another custom escape character
SELECT * FROM tags WHERE name LIKE '100#% complete' ESCAPE '#';

-- IMPORTANT: always escape user input before using in LIKE
-- In application code (PHP example):
-- $term = str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $userInput);
-- $query = "SELECT * FROM products WHERE name LIKE ?";
-- bind: '%' . $term . '%'

-- Assume: INDEX idx_last_name (last_name) on customers table

-- GOOD: prefix match -- MySQL seeks the index range [Smith%, ...)
EXPLAIN SELECT * FROM customers WHERE last_name LIKE 'Smi%';
-- type: range   key: idx_last_name   rows: ~50

-- BAD: leading wildcard -- no index can help, full table scan
EXPLAIN SELECT * FROM customers WHERE last_name LIKE '%ith';
-- type: ALL   key: NULL   rows: 500000

-- BAD: substring match -- same problem, full scan
EXPLAIN SELECT * FROM customers WHERE last_name LIKE '%mith%';
-- type: ALL   key: NULL   rows: 500000

-- Rule of thumb: LIKE 'prefix%' is fast; LIKE '%suffix' or LIKE '%contains%' are slow

-- Only prefix patterns (no leading wildcard) use a B-tree index
-- Create a suitable index
ALTER TABLE products ADD INDEX idx_name (name);

-- Index USED: pattern starts with a literal string
EXPLAIN SELECT * FROM products WHERE name LIKE 'Wireless%';
-- type: range   Extra: Using index condition

-- Index NOT used: pattern starts with a wildcard
EXPLAIN SELECT * FROM products WHERE name LIKE '%Wireless';
-- type: ALL   (full scan, index ignored)

-- Index NOT used: function applied to the column
EXPLAIN SELECT * FROM products WHERE LOWER(name) LIKE 'wireless%';
-- type: ALL   (function prevents index use)

-- Fix for case-insensitive prefix search without function wrapping:
-- use a case-insensitive collation on the column (default in MySQL)
EXPLAIN SELECT * FROM products WHERE name LIKE 'wireless%';
-- type: range   (works if collation is _ci, LIKE is already case-insensitive)

-- Anchored match: string must start with a digit
SELECT * FROM products WHERE sku REGEXP '^[0-9]';

-- Match emails (basic validation)
SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$';

-- Alternation: match 'cat' or 'dog'
SELECT * FROM posts WHERE content REGEXP 'cat|dog';

-- Quantifiers: 3 to 5 digits
SELECT * FROM codes WHERE code REGEXP '^[0-9]{3,5}$';

-- Character class: only alphanumeric and underscore
SELECT * FROM users WHERE username REGEXP '^[a-zA-Z0-9_]+$';

-- Case-sensitive regex
SELECT * FROM products WHERE sku REGEXP BINARY '^[A-Z]{2}[0-9]{4}$';

-- NOT REGEXP
SELECT * FROM users WHERE username NOT REGEXP '[^a-zA-Z0-9_]';  -- no special chars

-- REGEXP_LIKE(string, pattern [, match_type])
-- Returns 1 if string matches pattern, 0 otherwise
SELECT REGEXP_LIKE('Hello World', 'hello', 'i');  -- 1 (case-insensitive)
SELECT REGEXP_LIKE('Hello World', 'hello');        -- 0 (case-sensitive by default)

-- REGEXP_INSTR(string, pattern): position of the first match
SELECT REGEXP_INSTR('abc 123 def', '[0-9]+');  -- returns 5

-- REGEXP_SUBSTR(string, pattern): extract the matched substring
SELECT REGEXP_SUBSTR('Order #12345 placed', '[0-9]+');  -- returns '12345'

-- REGEXP_REPLACE(string, pattern, replacement): replace matches
SELECT REGEXP_REPLACE('Hello   World', ' {2,}', ' ');  -- 'Hello World'

-- Practical: extract the domain from an email
SELECT email,
  REGEXP_SUBSTR(email, '@(.+)$') AS domain_part
FROM users
LIMIT 5;

-- Practical: mask a credit card number keeping last 4 digits
SELECT REGEXP_REPLACE(card_number, '[0-9](?=[0-9]{4})', '*') AS masked
FROM payments;

-- Create a FULLTEXT index on the columns you want to search
ALTER TABLE products ADD FULLTEXT INDEX ft_search (name, description);

-- Natural language search (ranked by relevance)
SELECT id, name,
  MATCH(name, description) AGAINST ('wireless bluetooth headphones') AS relevance
FROM products
WHERE MATCH(name, description) AGAINST ('wireless bluetooth headphones')
ORDER BY relevance DESC
LIMIT 10;

-- Boolean mode: fine-grained control
-- + required term, - excluded term, * prefix wildcard, "" phrase
SELECT id, name
FROM products
WHERE MATCH(name, description)
  AGAINST ('+wireless +bluetooth -wired' IN BOOLEAN MODE);

-- Phrase match
SELECT id, name
FROM products
WHERE MATCH(name, description)
  AGAINST ('"noise cancelling"' IN BOOLEAN MODE);

-- PROBLEM: LIKE '%wireless%' is slow on a 1M-row products table
EXPLAIN SELECT * FROM products WHERE name LIKE '%wireless%';
-- type: ALL   rows: 1000000   -- full scan every time

-- SOLUTION 1: if prefix match is acceptable, add an index
ALTER TABLE products ADD INDEX idx_name (name);
EXPLAIN SELECT * FROM products WHERE name LIKE 'wireless%';
-- type: range   rows: ~200   -- index range scan

-- SOLUTION 2: full-text index for true contains/relevance search
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT id, name
FROM products
WHERE MATCH(name) AGAINST ('wireless' IN BOOLEAN MODE);
-- Uses the fulltext index, much faster than LIKE '%wireless%'

-- Add a stored generated column with the reversed value
ALTER TABLE products
  ADD COLUMN sku_reversed VARCHAR(50)
    GENERATED ALWAYS AS (REVERSE(sku)) STORED;

ALTER TABLE products ADD INDEX idx_sku_rev (sku_reversed);

-- Now suffix search on SKU uses the index via a prefix search on the reversed column
SELECT * FROM products
WHERE sku_reversed LIKE REVERSE('%001');
-- Equivalent to: WHERE sku LIKE '%001' but with an index seek

-- Similarly for email domain search
ALTER TABLE users
  ADD COLUMN email_reversed VARCHAR(255)
    GENERATED ALWAYS AS (REVERSE(email)) STORED;
ALTER TABLE users ADD INDEX idx_email_rev (email_reversed);

SELECT * FROM users
WHERE email_reversed LIKE REVERSE('%@company.com');

-- INSTR(string, substring): returns position of first occurrence (0 if not found)
SELECT * FROM products WHERE INSTR(description, 'wireless') > 0;
-- Equivalent to: WHERE description LIKE '%wireless%'
-- Same performance (full scan) but sometimes more readable

-- LOCATE(substring, string): same as INSTR with arguments reversed
SELECT * FROM products WHERE LOCATE('wireless', description) > 0;

-- INSTR is useful for multi-word checks without regex complexity
SELECT * FROM articles
WHERE INSTR(title, 'MySQL') > 0
  AND INSTR(title, 'performance') > 0;
-- Both words must appear anywhere in title

-- Application-side safe LIKE: escape user input, then bind
-- Pseudocode (PHP):
-- $term = addcslashes($userInput, '%_\\');
-- $stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ?");
-- $stmt->execute(['%' . $term . '%']);

-- MySQL stored procedure with safe escaping
DELIMITER $$
CREATE PROCEDURE search_products(IN p_term VARCHAR(200))
BEGIN
  -- Replace % and _ in the user's term to prevent wildcard injection
  SET @safe = REPLACE(REPLACE(p_term, '%', '\%'), '_', '\_');
  SET @sql  = CONCAT('SELECT * FROM products WHERE name LIKE "%', @safe, '%"');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

CALL search_products('50% off');   -- safe: looks for literal "50% off"
CALL search_products('t_shirt');   -- safe: looks for literal "t_shirt"

-- Search across multiple columns without FULLTEXT
-- This is a full scan on each column (slow on large tables)
SELECT id, first_name, last_name, email
FROM users
WHERE first_name LIKE '%alice%'
   OR last_name  LIKE '%alice%'
   OR email      LIKE '%alice%';

-- Better: FULLTEXT across multiple columns
ALTER TABLE users ADD FULLTEXT INDEX ft_name_email (first_name, last_name, email);

SELECT id, first_name, last_name, email
FROM users
WHERE MATCH(first_name, last_name, email)
  AGAINST ('alice' IN BOOLEAN MODE);

-- Best for large-scale: dedicated search (Elasticsearch / Typesense)
-- synced from MySQL via binlog CDC or periodic export

-- User searches for "100% natural" expecting only exact matches
-- Without escaping, % is treated as a wildcard:
-- WHERE name LIKE '%100% natural%'  -- 100% matches any string, returns many false positives

-- Correct: escape % and _ in the search term before binding
-- PHP: $escaped = str_replace(['%', '_', '\\'], ['\\%', '\\_', '\\\\'], $userInput);
-- Java PreparedStatement handles SQL injection; you still need to escape wildcards manually

-- MySQL stored function to escape LIKE metacharacters
DELIMITER $$
CREATE FUNCTION escape_like(p_str VARCHAR(500)) RETURNS VARCHAR(600)
DETERMINISTIC
BEGIN
  SET p_str = REPLACE(p_str, '\\', '\\\\');
  SET p_str = REPLACE(p_str, '%',  '\\%');
  SET p_str = REPLACE(p_str, '_',  '\\_');
  RETURN p_str;
END$$
DELIMITER ;

-- Usage
SELECT * FROM products
WHERE name LIKE CONCAT('%', escape_like('100% natural'), '%');
-- Now '100% natural' is treated as a literal string, not a pattern