Topic 38 of 52
CONCAT, SUBSTRING
Overview
SQL string functions manipulate text data — combining, extracting, searching, and transforming strings. They are essential for data cleaning, formatting output, and building search functionality.
Syntax
sql
-- Concatenation
CONCAT('Hello', ' ', 'World') -- 'Hello World'
'Hello' || ' ' || 'World' -- PostgreSQL operator
first_name || ' ' || last_name -- full name
-- Substring extraction
SUBSTRING(str, start, length)
SUBSTRING('Hello World', 7, 5) -- 'World'
LEFT('Hello World', 5) -- 'Hello'
RIGHT('Hello World', 5) -- 'World'
-- Length
LENGTH('Hello') -- 5
CHAR_LENGTH('Hello') -- same (MySQL compatible)
-- Case conversion
UPPER('hello') -- 'HELLO'
LOWER('HELLO') -- 'hello'
INITCAP('hello world') -- 'Hello World' (PostgreSQL)
-- Trimming
TRIM(' hello ') -- 'hello'
LTRIM(' hello') -- 'hello'
RTRIM('hello ') -- 'hello'
-- Search and replace
REPLACE('hello world', 'world', 'SQL') -- 'hello SQL'
POSITION('world' IN 'hello world') -- 7
-- Padding
LPAD('42', 5, '0') -- '00042'
RPAD('hello', 10, '-') -- 'hello-----'Common Pitfalls
- String concatenation with NULL returns NULL in most databases — use COALESCE: COALESCE(field, '') || ' suffix'.
- String functions can prevent index usage — avoid wrapping indexed columns in functions in WHERE clauses.
- Interview tip: SPLIT_PART (PostgreSQL) or SUBSTRING_INDEX (MySQL) split strings by delimiter — useful for parsing structured text fields.
Real-World Example
Data cleaning and formatting for a customer export:
example
sql
-- Clean and format customer data for export
SELECT
u.id,
-- Standardize name
INITCAP(TRIM(u.first_name)) || ' ' || INITCAP(TRIM(u.last_name)) AS full_name,
-- Mask email (show first 3 chars and domain)
LEFT(u.email, 3) || '***@' ||
SUBSTRING(u.email, POSITION('@' IN u.email) + 1) AS masked_email,
-- Format phone
'+91-' || REGEXP_REPLACE(u.phone, '[^0-9]', '', 'g') AS formatted_phone,
-- Truncate bio to 100 chars with ellipsis
CASE
WHEN LENGTH(u.bio) > 100 THEN SUBSTRING(u.bio, 1, 97) || '...'
ELSE u.bio
END AS short_bio,
-- Generate username from email
LOWER(REPLACE(SPLIT_PART(u.email, '@', 1), '.', '_')) AS auto_username,
-- Pad user ID for display
'USR-' || LPAD(u.id::TEXT, 8, '0') AS display_id
FROM users u
WHERE u.deleted_at IS NULL;