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?

Why are you trying to reverse a string? That's usually a display or other frontend issue. English and _most_ other western languages are fine, but other character sets start complaining, depending on the column type and db setup (and even then you can sometimes run into problems). — Clockwork-Muse, Nov 28 '14 at 04:02
I want to extract fom a [a-zA-Z0-9] sting of variable lenght the last 8 chars. in my idea its easier to make a `REVERSE(SUBSTRING(REVERSE(LEFT_TO_REIGHT),1,8))` instead `SUBSTRING(LEFT_TO_RIGHT,LEN(LEFT_TO_RIGHT)-8,LEN(LEFT_TO_RIGHT))`. — Joe Platano, Nov 28 '14 at 23:35
You don't need to reverse a string, there's a `RIGHT(...)` function (see new answer). Seems you got too hung up on finding a specific solution, than looking to fulfill your requirements Personally, I'd have preferred the latter version, I think it reads better. — Clockwork-Muse, Nov 29 '14 at 02:37

score 3 · Accepted Answer · answered Nov 29 '14 at 02:33

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).

d'oh i really was not consider the RIGHT function. guess i was right blackouted. works perfectly — Joe Platano, Nov 29 '14 at 09:05

score 2 · Answer 2 · answered Nov 27 '14 at 13:21

2

Try something like:

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

answered Nov 27 '14 at 13:21

SMA

36,381
8
49
73

@joemexiko - A word of warning: for this to work the "translator" strings must be comprised of unique characters (and reversals of each other), that is as long as or **longer** than any value in the given column. A standard keyboard has ~96 different symbols that should work in all cases (there are far more available, but after the first hundred the db's ability to tell them apart depends on a few things you may have little control over). I have to admit I'd never thought of using `TRANSLATE(...)` this way, though. – Clockwork-Muse Nov 28 '14 at 03:59
@Clockwork-Muse Yes I realised this in a few test runs on columns up 9 digits its working fine. For SELECT over large columns on large tables via linked server from my mssql... well i will to test other approaches also ;). – Joe Platano Nov 28 '14 at 23:49

score 2 · Answer 3 · answered May 17 '20 at 09:11

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

score 1 · Answer 4 · answered Aug 22 '19 at 01:18

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;
}

String operation in SQL to reverse a string

4 Answers4