PostgreSQL String Functions
Master text manipulation in PostgreSQL
📝 What are String Functions?
String functions in PostgreSQL help you manipulate, format, and analyze text data. They allow you to change case, extract parts, combine strings, and search within text efficiently.
-- Basic string operations
SELECT UPPER('hello'); -- HELLO
SELECT LOWER('WORLD'); -- world
SELECT LENGTH('PostgreSQL'); -- 10
Output:
UPPER: HELLO
LOWER: world
LENGTH: 10
Common String Functions
Case Conversion
Change text case
SELECT UPPER('text');Substring
Extract parts of strings
SELECT SUBSTRING('Hello' FROM 1 FOR 3);Concatenation
Join strings together
SELECT CONCAT('Hello', ' ', 'World');Trimming
Remove extra spaces
SELECT TRIM(' text ');🔹 UPPER() and LOWER()
Convert strings to uppercase or lowercase. These functions are useful for standardizing text data, making case-insensitive comparisons, and formatting output consistently across your database queries.
-- Convert to uppercase
SELECT UPPER('hello world');
-- Returns: HELLO WORLD
-- Convert to lowercase
SELECT LOWER('HELLO WORLD');
-- Returns: hello world
-- Practical example
SELECT
name,
UPPER(name) AS uppercase_name,
LOWER(email) AS lowercase_email
FROM users;
Output:
| name | uppercase_name | lowercase_email |
|---|---|---|
| John Doe | JOHN DOE | [email protected] |
🔹 CONCAT() and ||
Combine multiple strings into one. PostgreSQL offers two ways to concatenate: the CONCAT function and the || operator. Both methods handle NULL values differently, so choose based on your needs.
-- Using CONCAT function
SELECT CONCAT('Hello', ' ', 'World');
-- Returns: Hello World
-- Using || operator
SELECT 'Hello' || ' ' || 'World';
-- Returns: Hello World
-- Combine columns
SELECT
first_name || ' ' || last_name AS full_name,
CONCAT(city, ', ', country) AS location
FROM customers;
Output:
Result: Hello World
🔹 SUBSTRING()
Extract a portion of a string starting from a specific position. This function is perfect for parsing codes, extracting initials, or getting specific parts of formatted text like phone numbers or IDs.
-- Basic substring
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 6);
-- Returns: Postgr
-- Alternative syntax
SELECT SUBSTRING('PostgreSQL', 1, 6);
-- Returns: Postgr
-- Extract from position to end
SELECT SUBSTRING('PostgreSQL' FROM 7);
-- Returns: SQL
-- Practical example
SELECT
product_code,
SUBSTRING(product_code FROM 1 FOR 3) AS category,
SUBSTRING(product_code FROM 4) AS item_number
FROM products;
Output:
SUBSTRING(1-6): Postgr
SUBSTRING(7+): SQL
🔹 LENGTH() and CHAR_LENGTH()
Count the number of characters in a string. LENGTH returns bytes while CHAR_LENGTH returns characters, which matters for multi-byte characters. Use these to validate input length or analyze text data.
-- Get string length
SELECT LENGTH('PostgreSQL');
-- Returns: 10
-- Character length (same for ASCII)
SELECT CHAR_LENGTH('PostgreSQL');
-- Returns: 10
-- Find long descriptions
SELECT
product_name,
LENGTH(description) AS desc_length
FROM products
WHERE LENGTH(description) > 100;
Output:
LENGTH: 10
🔹 TRIM(), LTRIM(), RTRIM()
Remove unwanted spaces or characters from strings. TRIM removes from both ends, LTRIM from the left, and RTRIM from the right. Essential for cleaning user input and standardizing data.
-- Remove spaces from both sides
SELECT TRIM(' Hello World ');
-- Returns: Hello World
-- Remove from left only
SELECT LTRIM(' Hello World ');
-- Returns: Hello World
-- Remove from right only
SELECT RTRIM(' Hello World ');
-- Returns: Hello World
-- Remove specific characters
SELECT TRIM(BOTH 'x' FROM 'xxxHelloxxx');
-- Returns: Hello
Output:
TRIM: Hello World
TRIM 'x': Hello
🔹 REPLACE()
Replace all occurrences of a substring with another string. This function is case-sensitive and replaces every match found. Useful for data cleaning, formatting, and text transformations.
-- Replace substring
SELECT REPLACE('Hello World', 'World', 'PostgreSQL');
-- Returns: Hello PostgreSQL
-- Remove characters (replace with empty)
SELECT REPLACE('123-456-7890', '-', '');
-- Returns: 1234567890
-- Clean data
SELECT
phone,
REPLACE(REPLACE(phone, '-', ''), ' ', '') AS clean_phone
FROM contacts;
Output:
REPLACE: Hello PostgreSQL
Remove dashes: 1234567890
🔹 POSITION() and STRPOS()
Find the position of a substring within a string. Returns the starting position (1-based) or 0 if not found. Both functions work identically but have different syntax styles.
-- Find position using POSITION
SELECT POSITION('SQL' IN 'PostgreSQL');
-- Returns: 7
-- Find position using STRPOS
SELECT STRPOS('PostgreSQL', 'SQL');
-- Returns: 7
-- Check if substring exists
SELECT
email,
CASE
WHEN POSITION('@' IN email) > 0 THEN 'Valid'
ELSE 'Invalid'
END AS email_status
FROM users;
Output:
Position of 'SQL': 7
🔹 LEFT() and RIGHT()
Extract characters from the beginning or end of a string. LEFT gets characters from the start, RIGHT from the end. Perfect for getting prefixes, suffixes, or fixed-length codes.
-- Get first 5 characters
SELECT LEFT('PostgreSQL', 5);
-- Returns: Postg
-- Get last 3 characters
SELECT RIGHT('PostgreSQL', 3);
-- Returns: SQL
-- Extract initials and codes
SELECT
name,
LEFT(name, 1) AS initial,
RIGHT(product_code, 4) AS item_id
FROM products;
Output:
LEFT(5): Postg
RIGHT(3): SQL
🔹 INITCAP()
Convert the first letter of each word to uppercase and the rest to lowercase. This function creates proper title case formatting, ideal for names, titles, and headings.
-- Convert to title case
SELECT INITCAP('hello world');
-- Returns: Hello World
SELECT INITCAP('JOHN DOE');
-- Returns: John Doe
-- Format names properly
SELECT
INITCAP(first_name) AS first_name,
INITCAP(last_name) AS last_name,
INITCAP(city) AS city
FROM customers;
Output:
INITCAP: Hello World