Reverse string

Library Snippets

Reverse string

Works with PostgreSQL

Tested on 8.2+

Written in

PL/pgSQL

Depends on

Nothing

The reverse(text) function returns the input string with all characters in reverse order.

This is mainly useful for suffix pattern matching via indexes. For example:

CREATE TABLE words (word text);
CREATE INDEX ON words (word text_pattern_ops);
CREATE INDEX ON words (reverse(word) text_pattern_ops);

And then write queries like this:

SELECT * FROM words WHERE word LIKE 'ag%ng' AND reverse(word) LIKE reverse('ag%ng');

PostgreSQL will automatically pick one, or both, of the indexes for execution; for example:

 Bitmap Heap Scan on words
   Filter: ((word ~~ 'ag%ng'::text) AND (reverse(word) ~~ 'gn%ga'::text))
   ->  BitmapAnd
         ->  Bitmap Index Scan on words_word_idx
               Index Cond: ((word ~>=~ 'ag'::text) AND (word ~<~ 'ah'::text))
         ->  Bitmap Index Scan on words_reverse_idx
               Index Cond: ((reverse(word) ~>=~ 'gn'::text) AND (reverse(word) ~<~ 'go'::text))

PostgreSQL 9.1+

PostgreSQL 9.1 and later already include a built-in version of the reverse(text) function (documented here)

Slower, portable SQL version

This is a simple and portable SQL-language implementation with no external dependencies. Compatible with PostgreSQL 8.2+.

CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$
SELECT array_to_string(ARRAY(
  SELECT substring($1, s.i,1) FROM generate_series(length($1), 1, -1) AS s(i)
  ), '');
$$ LANGUAGE SQL IMMUTABLE STRICT;

Source: http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#LIKE_optimalization

Portable PL/pgSQL version

This is a portable PL/pgSQL implementation with no external dependencies, faster than the above. Compatible with PostgreSQL 8.2+. It's a slightly tweaked version of a function that's been reposted many times on the mailing list, from 2005.

CREATE FUNCTION reverse(input text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
  result text = '';
  i int;
BEGIN
  FOR i IN 1..length(input) BY 2 LOOP
    result = substr(input,i+1,1) || substr(input,i,1) || result;
  END LOOP;
  RETURN result;
END$$;

Faster, PL/Perl version

This function is much faster than the above for longer inputs (10 and more characters), but incurs Perl interpreter overhead for each session calling it.

CREATE FUNCTION reverse(text) RETURNS text
LANGUAGE plperl IMMUTABLE STRICT AS
'return reverse $_[0]';

Source: http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#LIKE_optimalization

Fastest, C version: orafce extension

A faster version written in C is avaiblable in PGXN, in the orafce extension (Oracle compatibility functions): http://pgxn.org/dist/orafce/

Original Pgfoundry project is archived.