REVERSE()

Syntax

How it works

REVERSE() takes a single string and returns a new string with the characters in the opposite order, so REVERSE('SQL') yields LQS. It does not change the original data; like every scalar string function it produces a value you can select, filter on, or nest inside another expression.

The classic practical use is pulling out the part of a string that comes after the last occurrence of a delimiter. Functions such as POSITION() find the first match from the left, so to work from the right you reverse the string, find the delimiter near the front, then reverse the answer back. This is the standard trick for grabbing a file extension after the final dot or the last segment of a path.

REVERSE() is also a compact way to test for palindromes: a value reads the same forwards and backwards when it equals its own reverse. One important portability note is that SQLite has no built in REVERSE() function, so code that relies on it will not run there without a user defined function.

Examples

Reverse a simple string

SELECT REVERSE('database') AS reversed;

reversed
========
esabatad

Reverse a column value

SELECT name, REVERSE(name) AS backwards
FROM products;

name   | backwards
=======+==========
apple  | elppa
mango  | ognam
lemon  | nomel

Get the file extension after the last dot

-- Reverse, find the first dot from the front (the last
-- dot in the original), take what is before it, reverse back
SELECT filename,
       REVERSE(SUBSTRING(REVERSE(filename), 1,
               POSITION('.' IN REVERSE(filename)) - 1)) AS ext
FROM documents;

filename       | ext
===============+=====
report.final.pdf | pdf
archive.tar.gz   | gz
notes.txt        | txt

Palindrome check

SELECT word,
       CASE WHEN word = REVERSE(word)
            THEN 'yes' ELSE 'no' END AS is_palindrome
FROM words;

word  | is_palindrome
======+==============
level | yes
radar | yes
hello | no

Sort by the end of a string

-- Order emails by their reversed form to group
-- similar domains and suffixes together
SELECT email
FROM users
ORDER BY REVERSE(email);

email
==================
sam@abbaz.com
lee@codewithsql.com
zoe@codewithsql.com

Common mistakes

-- On a byte oriented charset, some engines reverse
-- by byte and split a multibyte character (an emoji
-- or an accented letter) into invalid fragments
SELECT REVERSE(nickname) FROM users;

-- Store the text as UTF8 and use a matching collation
-- so REVERSE works on whole characters, not raw bytes
SELECT REVERSE(CONVERT(nickname USING utf8mb4)) FROM users;

-- Fails on SQLite: no such function REVERSE
SELECT REVERSE(name) FROM products;

-- SQLite has no REVERSE. Register a user defined
-- function in your application, or avoid REVERSE and
-- use a portable approach for the same result.
SELECT name FROM products;  -- reverse in application code

Performance

REVERSE() is a cheap per row operation, but wrapping a column in it inside a WHERE clause makes the predicate non sargable: the database cannot use a normal index on the raw column and falls back to a full scan. If you frequently query by the end of a string, store a reversed copy in a persisted or generated column and index that instead.

The extension and last segment trick that nests REVERSE() around SUBSTRING() and POSITION() runs three function calls per row. That is fine for projection, but avoid it in filters over large tables. Precompute the derived value once when you can rather than recomputing it on every scan.

Interview questions

SELECT REVERSE(SUBSTRING(REVERSE(filename), 1,
       POSITION('.' IN REVERSE(filename)) - 1)) AS ext
FROM documents;

SELECT word
FROM words
WHERE word = REVERSE(word);