String Functions
beginneradvanced
2 min read
What are String Functions?
PostgreSQL provides many built-in functions for working with text data. They let you concatenate, search, extract, transform, and format strings directly in SQL.
Common Functions
CONCAT(str1, str2) -- Concatenate strings
LENGTH(str) -- Character count
UPPER(str) / LOWER(str) -- Change case
TRIM(str) -- Remove leading/trailing whitespace
SUBSTRING(str FROM n FOR len) -- Extract substring
REPLACE(str, from, to) -- Replace occurrences
LEFT(str, n) / RIGHT(str, n) -- First/last n characters
POSITION(sub IN str) -- Find substring positionWhen to Use
- Formatting names for display
- Extracting parts of strings (email domains, phone area codes)
- Cleaning and normalizing text data
- Building dynamic output in queries
Key Points
- || Operator — PostgreSQL concatenation:
'Hello' || ' ' || 'World'. - CONCAT — Handles NULLs gracefully (treats them as empty strings).
- 1-Based Indexing — SUBSTRING and POSITION use 1-based indexing.
- Immutable — String functions do not modify the original data; they return new values.
- Pattern Matching — For advanced text search, combine with LIKE, SIMILAR TO, or regex (~).
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
String Functions Challenge
Write a query that solve this task: combine student names into a full name.
Expected result
A full_name column combining first and last names.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are String Functions?
Initializing database...Each run starts from fresh sample data.
More Examples
Extract email domain
Get the domain part of each student email.
Initializing database...Each run starts from fresh sample data.
Clean and normalize text
Trim whitespace and convert to lowercase.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between CONCAT and ||?
|| returns NULL if any operand is NULL. CONCAT treats NULLs as empty strings, which is often more convenient.
Is LENGTH the same as CHAR_LENGTH?
Yes, in PostgreSQL LENGTH and CHAR_LENGTH are equivalent for string types. OCTET_LENGTH returns byte count instead.