PostgreSQL REVERSE Function

Summary: in this tutorial, you’ll learn how to reverse a string using the PostgreSQL REVERSE function.

PostgreSQL REVERSE function overview #

The REVERSE function accepts a string and returns a new string that is the reverse of the input string.

The syntax of the REVERSE function is as follows:

REVERSE(text)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The REVERSE function accepts one argument:

• text: the input string you want to reverse.

The function returns a string with an order of characters in the input string reversed. It returns NULL if the input string is NULL.

Basic PostgreSQL REVERSE function example #

The following example uses the REVERSE function to reverse the string 'abc':

SELECT
  REVERSE('abc') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 cbaCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Detecting palindromes #

A palindrome is a sequence of characters that reads the same forward and backward (ignoring spaces, punctuation, and capitalization).

For example, madam is a palindromic word. A famous palindromic phrase is: "A man, a plan, a canal, Panama!".

The following defines a function is_palindromic that returns true if a string is palindromic:

CREATE OR REPLACE FUNCTION is_palindromic (
   input_string TEXT
) 
RETURNS BOOLEAN 
AS 
$$
DECLARE
    cleaned_string TEXT;
BEGIN
    -- Remove non-alphanumeric characters and convert to lowercase
    cleaned_string := REGEXP_REPLACE(LOWER(input_string), '[^a-z0-9]', '', 'g');

    -- Compare the cleaned string with its reversed version
    IF cleaned_string = REVERSE(cleaned_string) THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How the function works:

• First, remove non-alphanumeric characters from the input string and convert it to lowercase.
• Second, compare the cleaned string with its reversed version.

The following example uses the is_palindromic function to verify if strings are palindromic:

SELECT
  is_palindromic ('A man, a plan, a canal, Panama!'),
  is_palindromic ('level'),
  is_palindromic ('man');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 is_palindromic | is_palindromic | is_palindromic
----------------+----------------+----------------
 t              | t              | fCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the REVERSE function to reverse a string.