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');

NUMERIC (Numeric-Aware)

Compares strings by extracting and comparing their numeric content. This is useful for sorting version numbers, file names with numbers, or any strings where numeric ordering matters:

-- Without NUMERIC collation: 'file10' sorts before 'file2' (lexicographic)
-- With NUMERIC collation: 'file2' sorts before 'file10' (numeric-aware)
SELECT * FROM files ORDER BY COLLATE(name, 'NUMERIC');

-- Comparing version strings
SELECT * FROM packages
WHERE COLLATE(version, 'NUMERIC') > COLLATE('1.9', 'NUMERIC');
-- Matches '1.10', '2.0', etc. (would miss '1.10' with lexicographic comparison)

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');

-- Numeric-aware sort for natural ordering
SELECT * FROM items ORDER BY COLLATE(name, 'NUMERIC');