What Are String Functions?

-- Basic string function usage
SELECT UPPER(first_name) AS upper_name,
       LOWER(last_name) AS lower_name,
       LENGTH(first_name) AS name_length
FROM employees;

UPPER and LOWER

-- Standardize email formats
SELECT LOWER(email) AS standardized_email
FROM customers;

-- Case-insensitive search
SELECT * FROM employees
WHERE UPPER(department) = UPPER('engineering');

-- Display formatting
SELECT CONCAT(UPPER(LEFT(first_name, 1)), LOWER(SUBSTRING(first_name, 2))) AS formatted_name
FROM employees;

LENGTH

-- Find long employee names
SELECT first_name, last_name,
       LENGTH(first_name) + LENGTH(last_name) AS total_length
FROM employees
WHERE LENGTH(first_name) + LENGTH(last_name) > 15;

-- Validate data quality
SELECT email,
       LENGTH(email) AS email_length
FROM customers
WHERE LENGTH(email) < 5;

SUBSTRING

-- Extract area codes from phone numbers
SELECT phone_number,
       SUBSTRING(phone_number, 1, 3) AS area_code
FROM employees;

-- Get first initial
SELECT CONCAT(SUBSTRING(first_name, 1, 1), '.', SUBSTRING(last_name, 1, 1)) AS initials
FROM employees;

-- Extract domain from email
SELECT email,
       SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM customers;

CONCAT

-- Full name from parts
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- Build formatted output
SELECT CONCAT(department, ' - ', job_title, ' (', employee_id, ')') AS employee_info
FROM employees;

-- CONCAT_WS for delimited strings
SELECT CONCAT_WS(', ', first_name, last_name, city) AS contact_info
FROM employees;

TRIM, LTRIM, RTRIM

-- Clean imported data
SELECT TRIM(first_name) AS cleaned_name,
       LENGTH(TRIM(first_name)) AS cleaned_length,
       LENGTH(first_name) AS original_length
FROM employees
WHERE first_name != TRIM(first_name);

-- Remove specific characters
SELECT TRIM(BOTH '0' FROM product_code) AS trimmed_code
FROM products;

REPLACE

-- Clean phone number formatting
SELECT phone_number,
       REPLACE(REPLACE(phone_number, '-', ''), ' ', '') AS clean_phone
FROM employees;

-- Redact sensitive data
SELECT CONCAT(LEFT(email, 2), '***@', SUBSTRING(email, LOCATE('@', email) + 1)) AS masked_email
FROM customers;

LEFT and RIGHT

-- Extract first 3 characters
SELECT LEFT(department, 3) AS dept_code
FROM employees;

-- Extract file extension
SELECT file_name,
       RIGHT(file_name, LENGTH(file_name) - LOCATE('.', REVERSE(file_name))) AS extension
FROM uploaded_files;

CHAR_LENGTH vs OCTET_LENGTH

-- Character length vs byte length
SELECT first_name,
       CHAR_LENGTH(first_name) AS char_count,
       OCTET_LENGTH(first_name) AS byte_count
FROM employees;

String Functions with Aggregates

-- Most common department names
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY UPPER(TRIM(department))
ORDER BY cnt DESC;

-- String length statistics
SELECT department,
       AVG(LENGTH(first_name)) AS avg_name_length,
       MIN(LENGTH(first_name)) AS shortest_name,
       MAX(LENGTH(first_name)) AS longest_name
FROM employees
GROUP BY department;

Pattern Matching with LIKE

-- Names starting with 'J'
SELECT * FROM employees
WHERE first_name LIKE 'J%';

-- Emails containing a specific domain
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

-- Phone numbers with specific pattern
SELECT * FROM employees
WHERE phone_number LIKE '___-___-____';

Real-World String Transformations

-- Generate username from name
SELECT CONCAT(LOWER(SUBSTRING(first_name, 1, 1)), LOWER(last_name)) AS username
FROM employees;

-- Parse full name into components
SELECT SUBSTRING_INDEX(full_name, ' ', 1) AS first_name,
       SUBSTRING_INDEX(full_name, ' ', -1) AS last_name
FROM customers;

-- Format currency strings
SELECT CONCAT('$', FORMAT(salary, 2)) AS formatted_salary
FROM employees;

Common String Function Reference