|
View previous topic :: View next topic
|
| Author |
Message |
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
Hi,
Do we have a reverse function in DB2 SQl to reverse the contents of the string variable .
Eg if var1 = 'PIUT'
expected output = 'TUIP'
i can't code a cobol program for the same as I am aware that COBOL has a reverse function. I ned to incorporate the logic in my SQL to achive this. If anyone aware of this please share your thought.
Thanks |
|
| Back to top |
|
 |
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| Rijit wrote: |
Hi,
Do we have a reverse function in DB2 SQl to reverse the contents of the string variable .
Eg if var1 = 'PIUT'
expected output = 'TUIP'
i can't code a cobol program for the same as I am aware that COBOL has a reverse function. I ned to incorporate the logic in my SQL to achive this. If anyone aware of this please share your thought.
Thanks |
Hi any update techies? |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| Not yet, but i'm thinking hard. That ok with you? |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| PeterHolland wrote: |
| Not yet, but i'm thinking hard. That ok with you? |
Mee 2 thinking very hard. But not much footfall on my post so dissapointed..are all the techies sleeping   |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| just curious what could be the business need to reverse something |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
According to the version 10 SQL reference manual, you can define a UDF to do this:
| Code: |
Example 2: Define a scalar function that returns the text of an input string, in
reverse order:
CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
RETURNS VARCHAR(4000)
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN
DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT ’’;
DECLARE LEN INT;
IF INSTR IS NULL THEN
RETURN NULL;
END IF;
SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));
WHILE LEN > 0 DO
SET (REVSTR, RESTSTR, LEN)
= (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR,
SUBSTR(RESTSTR, 2, LEN - 1),
LEN - 1);
END WHILE;
RETURN REVSTR;
END# |
|
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| don.leahy wrote: |
According to the version 10 SQL reference manual, you can define a UDF to do this:
| Code: |
Example 2: Define a scalar function that returns the text of an input string, in
reverse order:
CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
RETURNS VARCHAR(4000)
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN
DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT ’’;
DECLARE LEN INT;
IF INSTR IS NULL THEN
RETURN NULL;
END IF;
SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));
WHILE LEN > 0 DO
SET (REVSTR, RESTSTR, LEN)
= (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR,
SUBSTR(RESTSTR, 2, LEN - 1),
LEN - 1);
END WHILE;
RETURN REVSTR;
END# |
|
Hi this is not working and giving errors.  |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| Can we use native DB2 function translate to do this string reverse? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Perhaps your field contains "srorre"?
What version of DB2 do you have? What exactly did you code and what exactly was the output?
You've ignored enrico's question.
DB2 function, no. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| Rijit wrote: |
| Can we use native DB2 function translate to do this string reverse? |
Obviously not. Otherwise why would IBM have chosen to publish a REVERSE function as a sample UDF? |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| enrico-sorichetti wrote: |
| just curious what could be the business need to reverse something |
Hi,
The business need is there because it is part of a business conversion we are doing in our project and we migrating data to a new database and we need to convert the data of a particular field(reverse) it . |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
| Data Masking is it ? |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| enrico-sorichetti wrote: |
| just curious what could be the business need to reverse something |
When using RTL alphabets, it is necessary to invert the column for the ORDER BY to work properly.
It may be a reason.
I asked a DBA and he said we use field procedures for this. |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
Hi this is working for me. Thanks a ton! |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
Also , I tried below and it works well too for larger field.
| Code: |
SELECT
STRIP(CAST( TRANSLATE('QPONMLKJIHGFEDCBA','ROHITUMARJIKAR', 'ABCDEFGHIJKLMNOPQ')
AS VARCHAR(20) ))
FROM SYSIBM.SYSDUMMY1; |
Output:
| Code: |
1
--------------
RAKIJRAMUTIHOR |
|
|
| Back to top |
|
|
|
|
