String Functions

SELECT
  UPPER(name) AS name_upper,
  LOWER(name) AS name_lower
FROM employees;

UPPER and LOWER are essential for case-insensitive comparisons. Always normalize case before joining on name or email fields.

SELECT TRIM(name) AS clean_name
FROM employees;

-- LTRIM removes only left spaces:
SELECT LTRIM(name) FROM employees;

-- RTRIM removes only right spaces:
SELECT RTRIM(name) FROM employees;

Trailing/leading spaces are a very common data quality issue. Always TRIM before loading into reports or before joining on text columns.

SELECT email,
       SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM employees;

-- In PostgreSQL:
SELECT email, SPLIT_PART(email, '@', 2) AS domain
FROM employees;

LOCATE finds the position of "@". SUBSTRING extracts from that position + 1 to end. SPLIT_PART (PostgreSQL) is cleaner for delimiter-based splitting.

-- Using CONCAT:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- Using || (PostgreSQL, SQLite):
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

CONCAT joins strings. If any argument is NULL, CONCAT returns NULL in MySQL (use CONCAT_WS to handle NULLs). In PostgreSQL || is the concatenation operator.

SELECT product_name,
       LENGTH(product_name) AS name_length
FROM products
WHERE LENGTH(product_name) > 20;

LENGTH returns character count. In SQL Server use LEN (which ignores trailing spaces). Useful for data validation — checking if imported text is within expected bounds.

SELECT company_name,
       REPLACE(company_name, 'Ltd', 'Limited') AS clean_name
FROM companies;

REPLACE is case-sensitive in MySQL. For case-insensitive replace in PostgreSQL use REGEXP_REPLACE. Useful for standardising data during ETL/data cleaning.