MySQL String Functions and Text Analytics

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;          -- John Smith, David Brown, Lisa Taylor, Alex Wilson

SELECT CONCAT('EMP-', employee_id) AS employee_code
FROM employees;          -- EMP-101, EMP-102, EMP-103

SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name);   -- NULL

SELECT CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name) AS full_name
FROM employees;

SELECT CONCAT_WS(' - ', first_name, department) AS employee_info
FROM employees;          -- John - IT, David - HR, Lisa - Finance

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM employees;          -- John Smith (the NULL middle name is skipped)

SELECT first_name, LENGTH(first_name)      AS byte_length      FROM employees;
SELECT first_name, CHAR_LENGTH(first_name) AS character_length FROM employees;

SELECT UPPER(first_name) AS employee_name FROM employees;   -- JOHN, DAVID, LISA, ALEX
SELECT LOWER(email) AS normalized_email FROM employees;     -- JOHN.SMITH@GMAIL.COM → john.smith@gmail.com

SELECT LOWER(TRIM(email)) AS normalized_email
FROM employees;

SELECT TRIM(email)  FROM employees;   -- "  john@gmail.com  " → "john@gmail.com"
SELECT LTRIM(email) FROM employees;   -- left side only
SELECT RTRIM(email) FROM employees;   -- right side only

SELECT SUBSTRING(first_name, 1, 3)
FROM employees;                                -- Joh, Dav, Lis, Ale

SELECT SUBSTRING('john.smith@gmail.com', 12);  -- everything from position 12: gmail.com

SELECT LEFT(first_name, 2)  FROM employees;    -- Jo, Da, Li, Al
SELECT RIGHT(first_name, 2) FROM employees;    -- hn, id, sa, ex

SELECT RIGHT(account_number, 4) AS last_four_digits
FROM accounts;

SELECT REPLACE(email, '@gmail.com', '@company.com') AS company_email
FROM employees;          -- john.smith@gmail.com → john.smith@company.com

SELECT REPLACE(phone_number, '-', '') AS normalized_phone
FROM customers;          -- 987-654-3210 → 9876543210

SELECT LOCATE('@', email) AS at_position
FROM employees;          -- john.smith@gmail.com → 11 (0 if not found)

SELECT INSTR(email, '@')
FROM employees;          -- same result

SELECT REVERSE(first_name)
FROM employees;          -- John → nhoJ, David → divaD

SELECT *
FROM words
WHERE word = REVERSE(word);

SELECT * FROM employees WHERE first_name LIKE 'J%';     -- starts with J: John, James, Jennifer
SELECT * FROM employees WHERE first_name LIKE '%n';     -- ends with n
SELECT * FROM employees WHERE first_name LIKE '%it%';   -- contains "it"
SELECT * FROM employees WHERE first_name LIKE '_o%';    -- any one char, then "o", then anything: John

SELECT UPPER(CONCAT(first_name, ' ', last_name)) AS employee_name
FROM employees;          -- John + space + Smith → John Smith → JOHN SMITH

SELECT CONCAT('EMP-', employee_id, ' - ', UPPER(first_name)) AS employee_label
FROM employees;          -- EMP-101 - JOHN

-- Username: everything before @
SELECT LEFT(email, LOCATE('@', email) - 1) AS username
FROM employees;

-- Domain: everything after @
SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM employees;

SELECT SUBSTRING_INDEX(email, '@', 1)  AS username FROM employees;   -- john.smith
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain   FROM employees;   -- gmail.com

SELECT CONCAT(first_name, COALESCE(middle_name, ''), last_name) FROM employees;
SELECT CONCAT_WS(' ', first_name, middle_name, last_name)       FROM employees;   -- preferred

SELECT *
FROM employees
WHERE LOWER(first_name) = LOWER('JOHN');

-- 1. Full name
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM employees;

-- 2. Normalized emails
SELECT LOWER(TRIM(email)) AS normalized_email FROM employees;

-- 3. Customer search (flexible, but mind the leading wildcard)
SELECT * FROM customers WHERE customer_name LIKE '%Raj%';

-- 4. Employee code
SELECT CONCAT('EMP-', employee_id) AS employee_code FROM employees;

-- 5. Replace the email domain
SELECT REPLACE(email, '@gmail.com', '@company.com') AS company_email FROM employees;

-- 6. Extract the email domain
SELECT SUBSTRING_INDEX(email, '@', -1) AS email_domain FROM employees;

-- 7. Customers per email domain
SELECT SUBSTRING_INDEX(email, '@', -1) AS email_domain,
       COUNT(*) AS customer_count
FROM customers
GROUP BY SUBSTRING_INDEX(email, '@', -1)
ORDER BY customer_count DESC;

-- 8. Initials: John Smith → JS
SELECT CONCAT(LEFT(first_name, 1), LEFT(last_name, 1)) AS initials FROM employees;

-- 9. Normalize phone numbers
SELECT REPLACE(REPLACE(TRIM(phone_number), '-', ''), ' ', '') AS normalized_phone
FROM customers;

-- 10. Prefix search — more index-friendly than '%Raj%'
SELECT * FROM customers WHERE customer_name LIKE 'Raj%';

-- 1. Full names
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM employees;
-- 2. Emails in lowercase
SELECT LOWER(email) AS email FROM employees;
-- 3. Remove extra spaces from emails
SELECT TRIM(email) AS email FROM employees;
-- 4. Normalize emails completely
SELECT LOWER(TRIM(email)) AS normalized_email FROM employees;
-- 5. Email usernames
SELECT SUBSTRING_INDEX(email, '@', 1) AS username FROM employees;
-- 6. Email domains
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain FROM employees;
-- 7. Names starting with A
SELECT * FROM employees WHERE first_name LIKE 'A%';
-- 8. Employee codes
SELECT CONCAT('EMP-', employee_id) AS employee_code FROM employees;
-- 9. Replace Gmail domains with the company domain
SELECT REPLACE(email, '@gmail.com', '@company.com') AS company_email FROM employees;

-- Flexible substring search
SELECT customer_id, customer_name, email
FROM customers
WHERE customer_name LIKE CONCAT('%', :name, '%');

SELECT customer_id, customer_name, email
FROM customers
WHERE customer_name LIKE CONCAT(:name, '%');

SELECT customer_id, customer_name, email
FROM customers
WHERE normalized_email = :email;