String functions (UPPER LOWER LENGTH)
String functions transform and inspect text.
In this page:
Syntax
SELECT UPPER(column), LOWER(column), LENGTH(column), SUBSTR(column, start, length)
FROM table_name;String functions (UPPER LOWER LENGTH)
UPPER and LOWER change case, LENGTH counts characters, SUBSTR (or SUBSTRING) extracts part of a string, TRIM removes surrounding spaces, REPLACE swaps text and || joins strings.
PostgreSQL adds many more such as INITCAP, LEFT, RIGHT, SPLIT_PART and CONCAT_WS. The example uses portable functions.
Note:
PostgreSQL's INITCAP capitalises each word.
Example: String functions (UPPER LOWER LENGTH)
CREATE TABLE people (id INTEGER PRIMARY KEY, first TEXT, last TEXT);
INSERT INTO people VALUES (1, ' ada ', 'LOVELACE'), (2, 'alan', 'Turing');
SELECT UPPER(TRIM(first)) AS first_up, LOWER(last) AS last_low FROM people;
SELECT LENGTH(last) AS len, SUBSTR(last, 1, 3) AS first3 FROM people;
SELECT TRIM(first) || ' ' || last AS full_name FROM people;
SELECT REPLACE(last, 'ing', 'ONG') AS replaced FROM people;
-- Output:
-- first_up | last_low
-- ADA | lovelace
-- ALAN | turing
-- len | first3
-- 8 | LOV
-- 6 | Tur
-- full_name
-- ada LOVELACE
-- alan Turing
-- replaced
-- LOVELACE
-- TurONGLogin to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Using + to join strings
- Forgetting strings are 1-indexed in SUBSTR
- Applying functions to indexed columns and losing index use
Chapter Summary
- UPPER, LOWER, LENGTH
- SUBSTR extracts, TRIM cleans
- REPLACE swaps text
- || concatenates
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: