String Functions
BeginnerSQL string functions (CONCAT, SUBSTRING, TRIM, UPPER, LOWER, REPLACE, LENGTH, REGEXP) manipulate text columns for data cleaning, formatting, and search operations.
Overview
String functions are evaluated row-by-row during the SELECT or WHERE phase. Using a function on an indexed column in a WHERE predicate typically prevents index use — always try to rewrite to use the indexed form or create an expression index. CONCAT is ANSI-standard; MySQL also supports the || operator (PostgreSQL uses || natively). TRIM removes leading/trailing whitespace (common in ETL pipelines). SUBSTRING (or SUBSTR) extracts a portion of a string. REGEXP (MySQL) and ~ (PostgreSQL) allow regular expression matching. String functions are often misused to work around bad data modelling — storing CSV in a column and using string splitting, for example, is a red flag.
Core String Functions
These functions cover the vast majority of real-world text manipulation tasks in reporting queries, data migrations, and API response formatting.
-- PostgreSQL / MySQL string functions
-- CONCAT: join strings (CONCAT_WS includes separator, skips NULLs)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
SELECT CONCAT_WS(', ', city, state, country) AS address FROM users;
-- UPPER / LOWER: normalise for case-insensitive comparison
SELECT id FROM users WHERE LOWER(email) = 'alice@example.com';
-- Better: use a case-insensitive collation or expression index
-- TRIM / LTRIM / RTRIM: remove whitespace (important in ETL)
UPDATE users SET email = TRIM(email) WHERE email != TRIM(email);
-- LENGTH / CHAR_LENGTH: byte length vs character length (matters for multi-byte UTF-8)
SELECT id, CHAR_LENGTH(description) FROM products WHERE CHAR_LENGTH(description) > 500;
-- SUBSTRING: extract part of a string (1-based index)
SELECT SUBSTRING(phone, 1, 3) AS country_code FROM users; -- e.g. '+91'
-- REPLACE: substitute occurrences
SELECT REPLACE(description, 'old_brand', 'new_brand') FROM products;
-- POSITION / STRPOS: find first occurrence
SELECT POSITION('@' IN email) AS at_pos FROM users; -- standard
SELECT STRPOS(email, '@') AS at_pos FROM users; -- PostgreSQL aliasREGEXP and Anti-Patterns
Regular expressions handle complex patterns but bypass indexes. Using string functions on indexed columns in WHERE predicates forces full table scans — a frequent performance bug.
-- PostgreSQL REGEXP (~): validate Indian mobile numbers
SELECT id, phone FROM users WHERE phone ~ '^+91[6-9][0-9]{9}$';
-- MySQL REGEXP
SELECT id, phone FROM users WHERE phone REGEXP '^\+91[6-9][0-9]{9}$';
-- Anti-pattern: function on indexed column → sequential scan
-- Bad: index on email is NOT used
SELECT id FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
-- Fix 1: expression index
CREATE INDEX idx_users_email_upper ON users(UPPER(email));
-- Fix 2: store email in lower-case on insert (application layer)
-- Fix 3: use ILIKE (PostgreSQL) — still no standard index, but pg_trgm helps
-- Anti-pattern: storing CSVs and splitting at query time
SELECT id, value FROM products
WHERE ',' || tags || ',' LIKE '%,electronics,%'; -- full scan, unmaintainable
-- Fix: normalise to a junction table (product_tags)
-- String aggregation: collapse many rows into one comma-separated value
SELECT user_id, STRING_AGG(product_name, ', ' ORDER BY product_name) AS purchased
FROM order_items oi JOIN products p ON p.id = oi.product_id
GROUP BY user_id; -- PostgreSQL: STRING_AGG | MySQL: GROUP_CONCATKey Points to Remember
- 1String functions on indexed columns in WHERE prevent index use — create expression indexes.
- 2CONCAT_WS skips NULL values and inserts a separator — cleaner than nested CONCALTs.
- 3CHAR_LENGTH returns character count; LENGTH returns byte count (different for UTF-8 multi-byte chars).
- 4TRIM is essential in ETL pipelines where source data contains leading/trailing spaces.
- 5STRING_AGG (PostgreSQL) / GROUP_CONCAT (MySQL) collapse rows into a delimited string.
- 6Storing CSV in a column and parsing with string functions is an anti-pattern — normalise instead.
Interview Questions
Sign in to ask AriaWhy does using UPPER(email) in a WHERE clause prevent index usage?
What is the difference between LENGTH and CHAR_LENGTH in MySQL?
How would you aggregate multiple rows into a comma-separated list in PostgreSQL vs MySQL?
What are the performance implications of using REGEXP in a WHERE clause?
Ask Aria about String Functions
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.