SQL String Functions

String functions are essential for cleaning, transforming, and analyzing text data in SQL. Each DBMS offers slightly different syntax, but the core idea is the same.

🔹 Common String Functions with Examples

-- Convert to upper/lower case
SELECT UPPER('sql tutorial');     -- SQL TUTORIAL
SELECT LOWER('SQL TUTORIAL');     -- sql tutorial

-- Get string length
SELECT LENGTH('Hello');           -- MySQL/PostgreSQL
SELECT LEN('Hello');              -- SQL Server

-- Concatenate strings
SELECT CONCAT(first_name, ' ', last_name) FROM employees;      -- MySQL/PostgreSQL
SELECT first_name + ' ' + last_name FROM employees;            -- SQL Server

-- Substring
SELECT SUBSTRING('Database', 1, 4);    -- Data (All DBs)
SELECT SUBSTR('Database', 1, 4);       -- MySQL/PostgreSQL alternative

-- Replace text
SELECT REPLACE('Learn SQL', 'SQL', 'Python');  -- Learn Python

-- Trim spaces
SELECT TRIM('  SQL  ');               -- SQL (removes both ends)
SELECT LTRIM('  SQL');                -- Removes leading spaces
SELECT RTRIM('SQL  ');                -- Removes trailing spaces

-- Position of substring
SELECT POSITION('a' IN 'Database');   -- 2 (PostgreSQL)
SELECT CHARINDEX('a', 'Database');    -- 2 (SQL Server)

🔹 Useful Real-World Examples

-- Get domain from email
SELECT SUBSTRING(email, POSITION('@' IN email) + 1) FROM users;

-- Concatenate first and last name
SELECT CONCAT_WS(' ', first_name, last_name) FROM employees;  -- MySQL

-- Clean user input
SELECT TRIM(LOWER(username)) FROM users;

🔹 Common Functions by DBMS

🧠 Quick Recap

💡 Combine string functions to clean and format output for reports, UI, and validation rules.