String operation in SQL to reverse a string

Question

In DB2 9.7 I am looking for a way to reverse a string in a SQL query.

I am familiar with SQL Server where the query would be like

SELECT
    REVERSE(LEFT_TO_REIGHT) AS RIGHT_TO_LEFT
FROM 
    TABLE1;

I couldn't find a similar function in DB2. is there a simple way to reverse a string?

Answer 1

Creating a REVERSE(..) function is unnecessary.
DB2 has something called RIGHT(string-expression, length):

The RIGHT function returns the rightmost string of string-expression of length length, expressed in the specified string unit. If string-expression is a character string, the result is a character string. If string-expression is a graphic string, the result is a graphic string

So if you're interested in the last 8 characters, you can pretty trivially do this via:

SELECT RIGHT(left_to_right, 8) AS right_to_left
FROM Table1

(I'm actually still concerned about the fact that you're splitting off 8 characters consistently, as it implies you have a multi-part key of some sort).

Answer 2

Try something like:

SELECT STRIP(CAST( TRANSLATE('87654321',LEFT_TO_REIGHT, '12345678') AS VARCHAR(8) ))                                   
FROM TABLE1;   

Answer 3

Answering the original question of reversing a string there's user defined functions published on the IBM site that will do it that you can find here. There's apparently no built in ability in DB2

https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/reverse?lang=en

Tortured SQL version:

CREATE OR REPLACE FUNCTION REVERSE(INSTR VARCHAR(4000))
  RETURNS VARCHAR(4000) SPECIFIC REVERSE
   DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
 RETURN WITH rec(pos, res) AS (VALUES (1, CAST('' AS VARCHAR(4000)))
                               UNION ALL
                               SELECT pos + 1, SUBSTR(INSTR, pos , 1) || res 
                                 FROM rec 
                                 WHERE pos <= LENGTH(INSTR) 
                                   AND pos < 5000)
        SELECT res FROM rec WHERE pos > LENGTH(INSTR);

But then you have to do this as well, yuck:

CREATE BUFFERPOOL bp32 PAGESIZE 32K;

CREATE SYSTEM TEMPORARY TABLESPACE tsp32 PAGESIZE 32K BUFFERPOOL bp32;

A saner C implementation

#include <sqludf.h>

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN ReverseSBCP(SQLUDF_VARCHAR  *inVarchar,
                            SQLUDF_VARCHAR  *outVarchar,
                            SQLUDF_SMALLINT *inVarcharNullInd,
                            SQLUDF_SMALLINT *outVarcharNullInd,
                            SQLUDF_TRAIL_ARGS)
{
  int inLen, inPos, outPos;

  if (*inVarcharNullInd == -1)
  {
    *outVarcharNullInd = -1;
  }
  else
  {
    inLen = strlen(inVarchar);
    for (inPos = 0, outPos = inLen -1; inPos < inLen; inPos++, outPos--)
    {   
      outVarchar[outPos] = inVarchar[inPos];
    }   
    outVarchar[inLen] = '\0'; 
    *outVarcharNullInd = 0;
  }
  return;
}

Answer 4

Due to the original question this is the first webpage that comes up when one searches for 'How to reverse a string in DB2'.

Here is an answer that doesn't require implementing it in C and shouldn't brake on non-pure-Engilsh strings regardless of their length. Be warned though, the efficiency is 'meh' at best.

CREATE FUNCTION REVERSE_STRING(STR VARCHAR(100))
RETURNS VARCHAR(100)
LANGUAGE SQL
SPECIFIC REVERSE_STRING
DETERMINISTIC
REVERSE: BEGIN
DECLARE REVERSED_STRING VARCHAR(100);
DECLARE REVERSED_CHARACTERS_INDEX INTEGER;
SET REVERSED_STRING='';
SET REVERSED_CHARACTERS_INDEX=0;
WHILE (REVERSED_CHARACTERS_INDEX < CHARACTER_LENGTH(STR, CODEUNITS16))
DO
        SET REVERSED_CHARACTERS_INDEX = REVERSED_CHARACTERS_INDEX + 1;
        SET REVERSED_STRING = CONCAT(
            REVERSED_STRING,
            LEFT(RIGHT(STR, REVERSED_CHARACTERS_INDEX, CODEUNITS16), 1, CODEUNITS16));
END WHILE;
RETURN REVERSED_STRING;
END REVERSE@

The idea is to get a substring which starts from the n-th character from the right till the end of the string, then take the first element of this substring from the left and append it to a reversed string. This operation is conducted n times where n is the length of a string to be reversed.

You can use it like any other function.

SELECT FIRSTNME AS FIRSTNAME, REVERSE_STRING(FIRSTNME) AS REVERSED_FIRSTNAME
FROM SAMPLE.EMPLOYEE@

Example output