String Functions (UPPER, LOWER, CONCAT, TRIM)
SQL string functions manipulate text data - cleaning, formatting, extracting, and combining strings. These are essential for data transformation, normalization, and presentation in reports and APIs.
Essential String Functions
- UPPER(str) - uppercase: UPPER('hello') = 'HELLO'
- LOWER(str) - lowercase: LOWER('HELLO') = 'hello'
- LENGTH(str) - character count: LENGTH('SQL') = 3
- CHAR_LENGTH(str) - same as LENGTH for most use cases
- CONCAT(s1, s2, ...) - join strings: CONCAT('Hello', ' ', 'World')
- TRIM(str) - remove leading and trailing spaces
- LTRIM / RTRIM - left or right trim only
- SUBSTRING(str, start, length) - extract substring
- REPLACE(str, old, new) - replace occurrences
- POSITION(substring IN str) - find position of substring
- LEFT(str, n) / RIGHT(str, n) - get first/last n characters
String Function Examples
-- Normalize user names and emails
SELECT
UPPER(name) AS name_upper,
LOWER(email) AS email_lower,
LENGTH(name) AS name_length,
TRIM(name) AS name_trimmed
FROM users;
-- Concatenate full display text
SELECT
CONCAT(name, ' <', email, '>') AS contact,
CONCAT('Order #', id, ' - ', status) AS order_label
FROM orders o JOIN users u ON o.user_id = u.id;
-- Extract email domain
SELECT
email,
SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM users;
-- john@example.com → example.com
-- Replace in product names
SELECT REPLACE(name, 'Phone', 'Smartphone') AS updated_name
FROM products;
-- First 3 characters of status
SELECT id, LEFT(status, 3) AS status_code FROM orders;
-- 'delivered' → 'del', 'pending' → 'pen'
-- Clean dirty data: remove extra spaces
UPDATE users SET name = TRIM(name) WHERE name != TRIM(name);Quick Quiz
Tip
Tip
Practice String Functions UPPER LOWER CONCAT TRIM in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Window functions = aggregate without GROUP BY. Calculate across related rows.
Common Mistake
Warning
A common mistake with String Functions UPPER LOWER CONCAT TRIM is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of String Functions UPPER LOWER CONCAT TRIM from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- SQL string functions manipulate text data - cleaning, formatting, extracting, and combining strings.
- UPPER(str) - uppercase: UPPER('hello') = 'HELLO'
- LOWER(str) - lowercase: LOWER('HELLO') = 'hello'
- LENGTH(str) - character count: LENGTH('SQL') = 3