Reverse string in DB2

Hi,
 
  I have to reverse a string for a particular field. Is there a way to do it in DB2 UDB 8.2?

Example : ABCDE

Desired Output : EDCBA
pvsbandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
you will have to implement it yourself

why do you need this?
Kent OlsenDBACommented:
Hi pvsbandi,

You come up with some of the coolest questions.   :)

There is no "native" function to do it in DB2.  You'll have to write a function that reverses the string, or build a small recursive SQL to do it.  I'll be glad to help with either.


Or maybe there's another solution to the problem.  Can you describe what you need?  (Other than just a string reversal.)



Kent
pvsbandiAuthor Commented:
:).. i'm using Business Objects DeskI to create a cross tab report, and the requirement is to reverse the rows column.. Since, there is no way to reverse the text in BO, i was wondering if i can do it on the database side..
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Kent OlsenDBACommented:
Well, you can do it, but not with a simple REVERSE() function call from the sysibm schema.

Here's a recursive SQL to do it.  Just modify the first subselect to pick up the string that you want to reverse.

Kent

with rev (length, newstring, oldstring)
as
(
  select 0, cast ('' as varchar (200)), 'Some String' FROM sysibm.sysdummy1

  UNION ALL

  SELECT length+1, 
    newstring || case when length (oldstring) > 0 then right (oldstring, 1) else '' end,
    left (oldstring, length (oldstring) - 1)
  FROM rev
  where oldstring <> ''
)
select * from rev r
where r.length = (select max(length) from rev);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pvsbandiAuthor Commented:
Slick..as always! Thank you!
Dave FordSoftware Developer / Database AdministratorCommented:

The following function works beautifully for me. HTH!

CREATE FUNCTION Reverse (
  inString VARCHAR(100)
)
  RETURNS varchar(100)
  LANGUAGE SQL
  modifies sql data
  set option dbgview = *source

BEGIN
-- Scratch variables used for processing
DECLARE outputString VARCHAR(100);
DECLARE stringLength INT;
DECLARE loopCounter INT;
DECLARE charAtPos VARCHAR(1);

-- If the incoming string is NULL, return NULL
IF (inString IS NULL) THEN
  return NULL;
else          

  -- Initialize the scratch variables
  SET outputString = '';
  SET stringLength = LENGTH(inString); 
  SET loopCounter = stringLength;

  -- Loop over the string
  WHILE (loopCounter >= 0) DO
     -- Append a single character off the string
     SET outputString = outputString || SUBSTRING (inString, loopCounter, 1);

     SET loopCounter = loopCounter - 1;
  END WHILE;

  -- Return the final output
  if (outputString = '') then
     return NULL;
  else
     return outputString;
  end if;

End If;

END

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.