SQL REVERSE()

The SQL REVERSE() function reverses the order of characters in a given string. This function is useful for various text manipulation tasks, such as checking for palindromes, reversing text for formatting purposes, or data processing needs.

The REVERSE() function is widely supported across SQL databases, including SQL Server, MySQL, and PostgreSQL.

In this tutorial, we will go through SQL REVERSE() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL REVERSE() Function

The basic syntax of the SQL REVERSE() function is:

</>
Copy
REVERSE(string);

Each part of this syntax has a specific purpose:

  • string: The string or text value you want to reverse. This can be a column, variable, or text literal.

The REVERSE() function returns a new string with the characters in reverse order.

Setup for Examples: Creating the Database and Table

We’ll create a sample names table with a full_name column to demonstrate the REVERSE() function examples.

1. First, create a new database called company_data:

</>
Copy
CREATE DATABASE company_data;

2. Select the company_data database to work with:

</>
Copy
USE company_data;

3. Create a table named names with the full_name field:

</>
Copy
CREATE TABLE names (
    name_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100)
);

4. Insert sample data into the names table to use with the REVERSE() function examples:

</>
Copy
INSERT INTO names (full_name)
VALUES ('Anna'), ('Bob'), ('John Doe'), ('Eve'), ('Madam');

With this setup complete, we can run the REVERSE() function examples to test and view results in the names table.

SQL REVERSE() - Setup for examples

Examples: Using REVERSE() in SQL Queries

We’ll go through examples demonstrating the REVERSE() function in SQL, using sample text data from a names table with a full_name column.

SQL REVERSE() - names table for examples

1. Reversing a Full Name

To reverse each name in the full_name column:

</>
Copy
SELECT full_name, REVERSE(full_name) AS reversed_name
FROM names;

This query returns each full_name with characters reversed, helpful for text manipulation or data transformations.

SQL REVERSE() Example - Reversing a string value

2. Checking for Palindromes Using REVERSE()

To identify if a name is a palindrome (reads the same forward and backward):

</>
Copy
SELECT full_name,
       CASE 
           WHEN full_name = REVERSE(full_name) THEN 'Yes' 
           ELSE 'No' 
       END AS is_palindrome
FROM names;

This query checks if each full_name is identical to its reversed form, indicating if it’s a palindrome.

SQL REVERSE() Example - Checking for Palindromes Using REVERSE()

3. Reversing a Substring

To reverse only a part of a string, such as the first 5 characters in full_name:

</>
Copy
SELECT full_name, 
       CONCAT(REVERSE(SUBSTRING(full_name, 1, 5)), SUBSTRING(full_name, 6, LEN(full_name))) AS partially_reversed
FROM names;

This query reverses the first 5 characters of each full_name and keeps the rest unchanged.

In the following screenshot, we have used CHAR_LENGTH() function instead of LEN(), because LEN() is not available in MySQL server.

SQL REVERSE() Example - Reversing a Substring

4. Reversing with Concatenated Output

To reverse a string and concatenate it with a suffix, such as ” (reversed)”:

</>
Copy
SELECT full_name, 
       CONCAT(REVERSE(full_name), ' (reversed)') AS formatted_output
FROM names;

This query returns the reversed full_name with the suffix ” (reversed)” appended, creating a labeled output as shown in the following screenshot.

SQL REVERSE() Example - Reversing with Concatenated Output

FAQs for SQL REVERSE()

1. What does the SQL REVERSE() function do?

The REVERSE() function reverses the order of characters in a string, returning the text in reverse order.

2. Can REVERSE() handle NULL values?

If the input is NULL, REVERSE() returns NULL.

Reference: SQL NULL

3. Is REVERSE() supported in all SQL databases?

Yes, REVERSE() is supported in SQL Server, MySQL, PostgreSQL, and other major SQL databases.

4. Can REVERSE() be combined with other string functions?

Yes, REVERSE() can be combined with functions like CONCAT() and SUBSTRING() for more complex string manipulation.

5. Does REVERSE() modify the original data in the table?

No, REVERSE() does not modify the original data. It returns a new result with the reversed string.