SQL - REVERSE() Function | Application Architect

Key Insights

• The REVERSE() function inverts character order in strings, useful for palindrome detection, data validation, and specialized sorting operations • Implementation varies across database systems—SQL Server and Azure SQL support it natively, while MySQL, PostgreSQL, and Oracle require different approaches • Performance considerations matter when reversing large strings or applying REVERSE() in WHERE clauses, as it prevents index usage

Understanding REVERSE() Fundamentals

The REVERSE() function takes a string input and returns it with characters in reverse order. The basic syntax is straightforward:

REVERSE(string_expression)

In SQL Server and Azure SQL Database, this is a built-in function. Here’s a simple example:

SELECT REVERSE('Application') AS ReversedText;
-- Output: noitacilppA

SELECT REVERSE('12345') AS ReversedNumber;
-- Output: 54321

The function works with VARCHAR, NVARCHAR, and TEXT data types. It handles NULL values by returning NULL:

SELECT REVERSE(NULL) AS Result;
-- Output: NULL

Database-Specific Implementations

SQL Server and Azure SQL

SQL Server provides native REVERSE() support with optimal performance:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductCode VARCHAR(20),
    ProductName NVARCHAR(100)
);

INSERT INTO Products VALUES 
(1, 'ABC123', 'Widget Pro'),
(2, 'XYZ789', 'Gadget Max'),
(3, 'DEF456', 'Tool Elite');

SELECT 
    ProductCode,
    REVERSE(ProductCode) AS ReversedCode,
    ProductName
FROM Products;

Output:

ProductCode  ReversedCode  ProductName
ABC123       321CBA        Widget Pro
XYZ789       987ZYX        Gadget Max
DEF456       654FED        Tool Elite

PostgreSQL

PostgreSQL doesn’t have REVERSE() but offers the reverse() function in lowercase:

SELECT reverse('PostgreSQL') AS reversed_text;
-- Output: LQStsergsoP

-- Practical example with user data
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com');

SELECT 
    username,
    reverse(username) AS reversed_username,
    email
FROM users;

MySQL

MySQL includes REVERSE() as a string function:

SELECT REVERSE('MySQL') AS reversed;
-- Output: LQSyM

-- Working with UTF-8 characters
SELECT REVERSE('Café') AS reversed;
-- Output: éfaC

Oracle

Oracle requires a custom implementation using PL/SQL or the REVERSE() function available in Oracle 12c+:

-- Oracle 12c and later
SELECT REVERSE('Oracle') FROM DUAL;
-- Output: elcarO

-- For earlier versions, create a function
CREATE OR REPLACE FUNCTION reverse_string(p_string IN VARCHAR2)
RETURN VARCHAR2
IS
    v_reversed VARCHAR2(4000);
BEGIN
    FOR i IN REVERSE 1..LENGTH(p_string) LOOP
        v_reversed := v_reversed || SUBSTR(p_string, i, 1);
    END LOOP;
    RETURN v_reversed;
END;
/

SELECT reverse_string('Oracle') FROM DUAL;

Practical Use Cases

Palindrome Detection

Identifying palindromes is a common application for REVERSE():

-- SQL Server example
CREATE TABLE Words (
    Word VARCHAR(50)
);

INSERT INTO Words VALUES 
('radar'), ('hello'), ('level'), ('world'), ('noon'), ('test');

SELECT 
    Word,
    REVERSE(Word) AS Reversed,
    CASE 
        WHEN Word = REVERSE(Word) THEN 'Palindrome'
        ELSE 'Not Palindrome'
    END AS IsPalindrome
FROM Words;

Output:

Word    Reversed  IsPalindrome
radar   radar     Palindrome
hello   olleh     Not Palindrome
level   level     Palindrome
world   dlrow     Not Palindrome
noon    noon      Palindrome
test    tset      Not Palindrome

Data Validation and Cleaning

REVERSE() helps validate symmetric codes or identifiers:

CREATE TABLE TransactionCodes (
    TransactionID INT,
    Code VARCHAR(20),
    Status VARCHAR(20)
);

INSERT INTO TransactionCodes VALUES
(1, 'A12-21A', 'Valid'),
(2, 'B34-56C', 'Invalid'),
(3, 'X99-99X', 'Valid');

-- Validate symmetric transaction codes
SELECT 
    TransactionID,
    Code,
    SUBSTRING(Code, 1, 3) AS Prefix,
    REVERSE(SUBSTRING(Code, 5, 3)) AS SuffixReversed,
    CASE 
        WHEN SUBSTRING(Code, 1, 3) = REVERSE(SUBSTRING(Code, 5, 3))
        THEN 'Valid Format'
        ELSE 'Invalid Format'
    END AS ValidationResult
FROM TransactionCodes;

Specialized Sorting

Reverse sorting based on string endings:

CREATE TABLE Domains (
    DomainID INT,
    DomainName VARCHAR(100)
);

INSERT INTO Domains VALUES
(1, 'blog.example.com'),
(2, 'api.example.com'),
(3, 'shop.example.org'),
(4, 'mail.example.com');

-- Sort by domain extension (TLD)
SELECT 
    DomainName,
    REVERSE(DomainName) AS ReversedDomain
FROM Domains
ORDER BY REVERSE(DomainName);

This sorts domains by their extensions first, then by subdomain.

String Manipulation Patterns

Combining REVERSE() with other string functions:

-- Extract and reverse specific portions
SELECT 
    ProductCode,
    REVERSE(RIGHT(ProductCode, 3)) AS LastThreeReversed,
    REVERSE(LEFT(ProductCode, 3)) AS FirstThreeReversed
FROM Products;

-- Create mirror strings
SELECT 
    ProductName,
    ProductName + '-' + REVERSE(ProductName) AS MirrorString
FROM Products;

Performance Considerations

REVERSE() in WHERE clauses prevents index usage:

-- This query cannot use an index on ProductCode
SELECT * 
FROM Products
WHERE REVERSE(ProductCode) = '321CBA';

-- Better approach: store reversed values if frequently queried
ALTER TABLE Products ADD ReversedCode AS REVERSE(ProductCode) PERSISTED;

CREATE INDEX idx_reversed_code ON Products(ReversedCode);

-- Now this query can use the index
SELECT * 
FROM Products
WHERE ReversedCode = '321CBA';

For large datasets, consider computed columns:

CREATE TABLE LargeDataset (
    ID INT PRIMARY KEY,
    TextData VARCHAR(MAX),
    ReversedData AS REVERSE(TextData) PERSISTED
);

CREATE INDEX idx_reversed ON LargeDataset(ReversedData);

Handling Edge Cases

Empty Strings and Whitespace

SELECT 
    REVERSE('') AS EmptyString,
    REVERSE('   ') AS Whitespace,
    REVERSE('  text  ') AS WithSpaces;
-- Output: '', '   ', '  txet  '

Unicode and Multibyte Characters

-- SQL Server handles Unicode correctly
SELECT REVERSE(N'日本語') AS JapaneseReversed;
-- Output: 語本日

-- Be cautious with combining characters
SELECT REVERSE(N'é') AS AccentedChar;  -- Single character
SELECT REVERSE(N'é') AS CombiningChar; -- e + combining acute

Maximum Length Constraints

-- REVERSE() maintains original length
DECLARE @LongString VARCHAR(MAX) = REPLICATE('A', 8000);
SELECT LEN(REVERSE(@LongString)) AS ReversedLength;
-- Output: 8000

Integration with Business Logic

Real-world example validating account numbers:

CREATE PROCEDURE ValidateAccountNumber
    @AccountNumber VARCHAR(20)
AS
BEGIN
    DECLARE @CheckDigit CHAR(1);
    DECLARE @ReversedBase VARCHAR(19);
    
    SET @CheckDigit = RIGHT(@AccountNumber, 1);
    SET @ReversedBase = REVERSE(LEFT(@AccountNumber, LEN(@AccountNumber) - 1));
    
    -- Custom validation logic using reversed string
    IF @CheckDigit = LEFT(@ReversedBase, 1)
        SELECT 'Valid' AS Result;
    ELSE
        SELECT 'Invalid' AS Result;
END;

The REVERSE() function is simple but powerful for specific data manipulation tasks. Use it judiciously, understanding its performance implications and database-specific behaviors.