reverse function in DB2 / how to reverse a string in DB2? 
Author Message
 reverse function in DB2 / how to reverse a string in DB2?
hi,

does anyone know the equivalent of reverse function in DB2?
following sql works in oracle but not in DB2.

select col_one from table_one where reverse(col_one) = col_one;

thanks
nutron



Mon, 09 Aug 2004 00:45:00 GMT
 reverse function in DB2 / how to reverse a string in DB2?

CREATE FUNCTION reverse(input VARCHAR(1000)) RETURNS VARCHAR(1000)
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN WITH rec(i, rev, org) AS
(VALUES (1, CASE WHEN input IS NOT NULL  THEN CAST('' AS VARCHAR(1000))
ELSE NULL END, input)
UNION ALL
SELECT i + 1, substr(org, 1, 1) || rev, substr(org, 2, length(org) -1)
FROM rec
WHERE i < 1000 AND org <> '' AND org IS NOT NULL)
SELECT rev FROM rec WHERE org = '' OR org IS NULL;

values (reverse('Hello World'));
=>
dlroW olleH

Cheers
Serge

PS: Of course you can also write it non relational in Java, C or SQL PL.

An unfenced C function would probably be the fastest...
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Mon, 09 Aug 2004 02:32:50 GMT
 reverse function in DB2 / how to reverse a string in DB2?
Another thought, in pseudo-code

CREATE RECURSIVE VARCHAR(1000)
       PROCEDURE Reverse(IN instring VARCHAR(1000))
AS
IF CHARLENGTH(instring) <= 1
THEN RETURN instring
ELSE RETURN Reverse (SUBSTRING instring
                          FROM CHARLENGTH (instring)/2 + 1
                           FOR CHARLENGTH (instring))
            || Reverse (SUBSTRING instring FROM 1 FOR CHARLENGTH
(instring)/2);

This could be improved with a CASE statement that has non-recursive
code for particular lengths of input strings.

CREATE RECURSIVE VARCHAR(1000)
       PROCEDURE Reverse(IN instring VARCHAR(1000))
AS
 CASE CHARLENGTH(instring)
 WHEN 0 THEN RETURN ''
 WHEN 1 THEN RETURN instring
 WHEN 2 THEN RETURN (SUBSTRING instring FROM 2 FOR 1)
              || (SUBSTRING instring FROM 1 FOR 1)
 WHEN 3 THEN RETURN (SUBSTRING instring FROM 3 FOR 1)
              || (SUBSTRING instring FROM 2 FOR 1)
              || (SUBSTRING instring FROM 1 FOR 1)
 ...
 ELSE RETURN Reverse (SUBSTRING instring
                          FROM CHARLENGTH (instring)/2 + 1
                           FOR CHARLENGTH (instring))
            || Reverse (SUBSTRING instring FROM 1 FOR CHARLENGTH
(instring)/2);

This will get me less than n*log2(n) performance, which stinks
compared to a simple loop.  It is just a good programming exercise.



Mon, 09 Aug 2004 07:47:58 GMT
 reverse function in DB2 / how to reverse a string in DB2?
... I wonder if it's possible to write Towers of Hanoi as a recursive
view and then print out the moves.... :-)

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Mon, 09 Aug 2004 08:51:26 GMT
 reverse function in DB2 / how to reverse a string in DB2?
Here's a cleaner one for you guys...
I think we can avoid testing the null input  when the
ON NULL input clause is not used.
I think the performance is N (the boucle mentionned by --CELKO--)
Ok, my version is not that educational.
It's based on Serge's reverse function.  I only  modified it a bit.
Less function calls, NULLs testing removed (probably unneeded?)

Code follows :
--
-- ORP : Onions Rewriter Project.
-- Goal : Automating the correction of detected onions (Serge TM) composed
of only 2 letters.
-- This function can also reverse a string but it was not the original goal.
--
-- Usage : REVERSE('OD')
-- Result : 'DO'
--
-- Usage : REVERSE('TI')
-- Result : 'IT'
--
-- Usage : values REVERSE('abasdflk56')
-- Result : '65klfdsaba'
--
CREATE FUNCTION REVERSE
(P_AST_IString VARCHAR(1000) )
RETURNS VARCHAR(1000)
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN
--
WITH rec( AST_WLastCharPos, AST_OReverse, AST_IString) AS (
--
 VALUES (
   length(P_AST_IString),
   CAST('' AS VARCHAR(1000)),
   P_AST_IString
 )
--
 UNION ALL
--
 SELECT
   AST_WLastCharPos - 1,
   concat(AST_OReverse, substr(AST_IString, AST_WLastCharPos, 1)),
   AST_IString
--
FROM rec
WHERE AST_WLastCharPos >= 1
)
--
SELECT AST_OReverse FROM rec where AST_WLastCharPos = 0
;

PM



Mon, 09 Aug 2004 21:32:26 GMT
 reverse function in DB2 / how to reverse a string in DB2?
Hi Pascal,

Yes, length(NULL)=> NULL, thus the WHERE clause in the final select will not be
true and not return any rows.
Sinc eit's a scalar fucntion you get NULL on empty behaviour.
The recursion will stop for the same reason (WHERE clause not true).

Now towers of hanoi ....

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Tue, 10 Aug 2004 04:54:33 GMT
 reverse function in DB2 / how to reverse a string in DB2?
Too much time, Knut? That can be fixed.... :-)
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada


Tue, 10 Aug 2004 22:27:30 GMT
 reverse function in DB2 / how to reverse a string in DB2?
I'm almost done with my sql XAND function.
I forecast pretty bad performance here but hey, i could be surprised?

I wish i could do some inline ASM in my SQL.  ;-)

I know i could do it with C but i'm practicing my sql-only solutions...
Mind games.  Pretty much like Hanoi.

Hanoi/Hanoy may be next.

PM



Wed, 11 Aug 2004 02:40:51 GMT
 reverse function in DB2 / how to reverse a string in DB2?

Quote:

> I'm almost done with my sql XAND function.
> I forecast pretty bad performance here but hey, i could be surprised?

Guess that depends what you call bad, but suspect you will be right.
:-)

I've had some very mixed experiences with SQL user functions -
sometimes much quicker than a Java equivalent, sometimes much slower.
The potential problem with the SQL user functions is that DB2 merges
the SQL in with the query that uses the function - rather like view
merge.  That can be a strength or a weakness, depending on how good
the optimiser is.  In practice, I've seen complex warehouse SQL
queries on a page or three long, rewritten to hideous 90 page queries
- once all the views and user functions are exploded.  No matter how
leading-edge the DB2 optimiser is, that tends to be too much - and
where rewriting the functions in a supported 3GL is far preferable.

Jeremy Rickard
United Systems (UK) Ltd.
http://www.unisystems.co.uk



Thu, 12 Aug 2004 08:46:06 GMT
 reverse function in DB2 / how to reverse a string in DB2?
Hi Jeremy,

Indeed, SQL Functions are mostly intended for macro-like complexity.
Which is why it's most beneficial to stick with as much pure SQL as possible.
Ultimately the goal is to merge SQL UDF and SQL Procedure Technology to a point where the SQL Compiler
decides whether you should get a "packaged" routine or an "inline" routine.
This would apply for both function invocation as well as procedure call.

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Thu, 12 Aug 2004 22:46:07 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. DB2 UDB V7.1 primary key constraint and allow reverse scans

2. Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2

3. Reversing string order

4. Parsing a string in reverse

5. reverse cumulative function ?

6. Incosistenices With Text Fields And Reverse Function

7. Delphi: Reverse Chr() to ASCII function?

8. Undocumented SQL REVERSE function.

9. Reverse engineering of compiled function or procedure

10. Hex function in reverse....

11. call COM-DLL function in DB2 (maybe from Trigger) - For datatransfer between DB2 and SQL Servers

12. Differences between SQL on DB2-MVS and DB2-OS/2 or DB2-NT


 
Powered by phpBB® Forum Software