Collation

Collation

Stoolap provides the COLLATE() function for controlling string comparison and sorting behavior. Four collation modes are supported.

Collation Modes

Mode	Aliases	Behavior
BINARY		Exact byte-level comparison (case-sensitive)
NOCASE	CASE_INSENSITIVE	Case-insensitive comparison
NOACCENT	ACCENT_INSENSITIVE	Accent-insensitive comparison (strips diacritical marks)
NUMERIC		Numeric-aware string comparison

Syntax

COLLATE(value, 'mode')

BINARY (Case-Sensitive)

Performs exact, case-sensitive comparison:

SELECT * FROM items WHERE COLLATE(name, 'BINARY') = 'Apple';
-- Matches 'Apple' only, NOT 'apple' or 'APPLE'

NOCASE (Case-Insensitive)

Ignores letter casing during comparison:

SELECT * FROM items
WHERE COLLATE(name, 'NOCASE') = COLLATE('apple', 'NOCASE');
-- Matches 'Apple', 'apple', 'APPLE'

NOACCENT (Accent-Insensitive)

Strips diacritical marks (accents) before comparison:

SELECT * FROM items
WHERE COLLATE(LOWER(name), 'NOACCENT') = COLLATE('cafe', 'NOACCENT');
-- Matches 'Cafe', 'cafe', 'CAFE'

Works with various accented characters:

-- Matches 'Nacao', 'nacao', 'NACAO'
SELECT * FROM items
WHERE COLLATE(LOWER(name), 'NOACCENT') = COLLATE('nacao', 'NOACCENT');

Combining Collations

Collation functions can be nested for combined effects:

-- Case-insensitive AND accent-insensitive
SELECT * FROM items
WHERE COLLATE(COLLATE(name, 'NOCASE'), 'NOACCENT')
    = COLLATE(COLLATE('nacao', 'NOCASE'), 'NOACCENT');

Collation in ORDER BY

Use collation to control sort order:

-- Case-insensitive sort: 'Apple', 'apple', 'APPLE' sort together
SELECT * FROM items ORDER BY COLLATE(name, 'NOCASE');