REVERSE

Reverse the order of characters in a string, or the order of elements in an array.

Category: stringReturns: VARCHAR | ARRAYDialect: Standard

Syntax

REVERSE(expr)

Description

## Overview Returns the input with its elements in reverse order. For string inputs, the character order is reversed so that the last character becomes the first and vice versa. For array inputs, the element order is reversed. A classic trick is to reverse a string before indexing it so that trailing-substring searches such as 'ends with .gov.uk' can be converted into leading-substring (LIKE '...') searches that use a B-tree index. REVERSE is also useful for palindrome checks and for building a key that orders items by their suffix. ## Behavior - Returns NULL when the input is NULL. - Returns an empty string when the input is an empty string, or an empty array when the input is an empty array. - Operates on Unicode code points, so multi-byte UTF-8 characters are reversed as whole units and the output is always valid UTF-8. - Combining sequences (base plus combining marks) are reversed by code point. The combining marks end up before the base character, which can render incorrectly; normalise to NFC first for visually stable reversal. - For arrays, element types and NULLs are preserved in their reversed position. ## Compatibility - REVERSE on strings is widely supported. - REVERSE on arrays matches the common SQL convention and is equivalent to ARRAY_REVERSE in some dialects.

Parameters

Name	Type	Description
`expr`		Specifies the input string or array to reverse. For a string, the character order is reversed. For an array, the element order is reversed.

Examples

-- Reverse a string
SELECT REVERSE('hello');  -- 'olleh'

-- Palindrome check
SELECT 'racecar' = REVERSE('racecar');  -- true

-- Empty string
SELECT REVERSE('');  -- ''

-- NULL propagates
SELECT REVERSE(CAST(NULL AS VARCHAR));  -- NULL

-- Suffix-first index: reverse a column so you can LIKE on a trailing pattern
SELECT REVERSE(email) AS email_reversed
FROM retail.customers.profiles;

-- Reverse an array
SELECT REVERSE(ARRAY[1, 2, 3]);  -- [3, 2, 1]

Pitfalls

Reversing a grapheme cluster (base letter plus combining marks) can produce output that renders incorrectly because combining marks move to the wrong side of the base. Apply NORMALIZE NFC first.
Indexing on REVERSE(col) requires a functional index; a plain index on col is useless for suffix searches. Create a GENERATED column if this pattern is frequent.
REVERSE is not a byte-level operation. A naive byte-reverse on UTF-8 would corrupt multi-byte characters; REVERSE always preserves valid UTF-8.
For arrays, REVERSE changes ordinal positions. If downstream queries rely on element offsets, those offsets will now point to different elements.