String Functions

The string functions implemented today, plus the || operator and LIKE / ILIKE. All of them return NULL when given NULL.

UPPER

UPPER(text) → TEXT

SELECT UPPER('héllo') AS u, UPPER(name) AS n FROM 'blog' WHERE path = '/hello';
-- {"u":"HÉLLO","n":"HELLO"}

Unicode case mapping; multi-byte characters are handled.

LOWER

LOWER(text) → TEXT

SELECT LOWER('HÉLLO') AS l;
-- {"l":"héllo"}

A common use is case-insensitive matching, though ILIKE does the same without a function call:

SELECT name FROM 'blog' WHERE LOWER(properties->>'title') LIKE '%first%';
SELECT name FROM 'blog' WHERE properties->>'title' ILIKE '%FIRST%';
-- both: first

COALESCE

Return the first argument that is not NULL. Any number of arguments; all must share a type.

COALESCE(value1, value2 [, ...]) → type of the arguments

SELECT COALESCE(NULL, 'default', 'other') AS c,
       COALESCE(properties->>'summary', properties->>'title') AS text
FROM 'blog' WHERE path = '/hello';
-- {"c":"default","text":"Hello"}

A missing JSON key reads as NULL through ->>, so COALESCE(properties->>'nickname', name) is the idiom for "use the property if the node has one".

NULLIF

Return NULL when the two arguments are equal, otherwise the first argument.

NULLIF(value1, value2) → type of value1

SELECT NULLIF('a', 'a') AS same, NULLIF('a', 'b') AS different;
-- {"same":null,"different":"a"}

-- treat an empty string as missing
SELECT COALESCE(NULLIF(properties->>'subtitle', ''), 'untitled') AS subtitle FROM 'blog';

-- avoid division by zero
SELECT (properties->>'total')::DOUBLE / NULLIF((properties->>'count')::INT, 0) AS average FROM 'blog';

Concatenation with ||

|| joins TEXT values. NULL in either operand makes the result NULL.

SELECT name || ' (' || path || ')' AS label FROM 'blog' WHERE path = '/hello';
-- {"label":"hello (/hello)"}

SELECT 'a' || NULL AS n;
-- {"n":null}

SELECT properties->>'title' || COALESCE(' - ' || properties->>'subtitle', '') AS heading FROM 'blog';

|| between two JSONB values is a merge, not a concatenation; see Operators.

LIKE and ILIKE

% matches any run of characters, _ exactly one. LIKE is case-sensitive, ILIKE is not; NOT LIKE negates.

SELECT name FROM 'blog' WHERE name LIKE 'h_llo';          -- hello
SELECT name FROM 'blog' WHERE name NOT LIKE 'h%';          -- news, first, second
SELECT name FROM 'blog' WHERE properties->>'title' ILIKE '%post%';   -- first

Regular-expression operators are not available today; use LIKE / ILIKE or full-text search (FULLTEXT_MATCH, Full-text functions).

Comparison and sorting

Text compares and sorts by Unicode code point, case-sensitively ('Z' < 'a'). MIN and MAX over text follow the same order:

SELECT MIN(name) AS first_name, MAX(name) AS last_name FROM 'blog';
-- {"first_name":"first","last_name":"second"}