String Functions¶

UPPER(str)¶

Converts a string to uppercase.

SELECT UPPER('hello');         -- 'HELLO'
SELECT UPPER(name) FROM users;

Converts a string to lowercase.

SELECT LOWER('HELLO');         -- 'hello'
SELECT LOWER(email) FROM users;

Returns the byte length of the string.

SELECT LENGTH('hello');        -- 5
SELECT LENGTH(name) FROM users;

Removes leading and trailing characters from a string. Defaults to whitespace.

SELECT TRIM('  hello  ');         -- 'hello'
SELECT TRIM('xxhelloxx', 'x');    -- 'hello'
SELECT TRIM(name) FROM users;

Removes leading characters only.

SELECT LTRIM('  hello  ');        -- 'hello  '
SELECT LTRIM('xxhello', 'x');     -- 'hello'

Removes trailing characters only.

SELECT RTRIM('  hello  ');        -- '  hello'
SELECT RTRIM('helloxx', 'x');     -- 'hello'

Returns a substring. start is 1-based. If length is omitted, returns to end of string.

SELECT SUBSTR('hello world', 7);      -- 'world'
SELECT SUBSTR('hello world', 1, 5);   -- 'hello'
SELECT SUBSTR(body, 1, 100) FROM articles;

Replaces all occurrences of from with to.

SELECT REPLACE('foo bar foo', 'foo', 'baz');  -- 'baz bar baz'
SELECT REPLACE(email, '@old.com', '@new.com') FROM users;

Concatenates strings. NULL arguments are silently skipped (PostgreSQL semantics).

SELECT CONCAT('hello', ' ', 'world');   -- 'hello world'
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

Alternatively, use the || operator:

SELECT first_name || ' ' || last_name FROM users;

All string functions return NULL if any non-skippable argument is NULL:

SELECT UPPER(NULL);         -- NULL
SELECT LENGTH(NULL);        -- NULL
SELECT REPLACE(NULL, 'a', 'b'); -- NULL

CONCAT is an exception — it skips NULL arguments and returns the concatenation of non-null values.

String functions can be used in expression indexes to accelerate function-based predicates:

CREATE INDEX idx_lower_email ON users (LOWER(email));

-- Uses the expression index
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';