REVERSE a string by using TRANSLATE (SQL)

Isaac Yassin

REVERSE a string by using TRANSLATE (SQL)
Hi,

Due to requests in previous thread.

The idea is to use the TRANSLATE function as a method to reverse strings (
"abcd" ==> "dcba"),
here's an example that can be used to reverse a string up to 8 bytes long:

CREATE FUNCTION REVERSE (INC VARCHAR(8))

RETURNS VARCHAR(8)

LANGUAGE SQL

CONTAINS SQL

NO EXTERNAL ACTION

DETERMINISTIC

RETURN
CAST( TRANSLATE(‘87654321’, INC, ’12345678’) AS VARCHAR(8) ) ;

Example:

select reverse('abcde') from sysibm.sysdummy1;

edcba



If you need to handle longer strings (up to 255) you may use x'00010203 ...
FCFDFE' :-) instead of '123...'

The good side - it works even on V7, no loop in handling (just one
assembler TR command).
The bad side - not fit for those 4k+ strings.


Isaac Yassin
IBM Gold Consultant
IBM Champion - Information Management
IBM Certified Solution Expert
IBM Certified System Administrator - DB2 10 for z/OS
IBM Certified Database Administrator - DB2 for z/OS V7, V8, 9 & 10
IDUG Israel RUG

Max Scarpa

REVERSE a string by using TRANSLATE (SQL)
(in response to Isaac Yassin)
Hi Isaac

Quite interesting, I'll try it in my DB2 soapbox. Thanks !

Max Scarpa
Certified Angel's Landing or bust !

Il 24/04/2012 18:34, Isaac Yassin ha scritto:
> Hi,
>
> Due to requests in previous thread.
>
> The idea is to use the TRANSLATE function as a method to reverse
> strings ( "abcd" ==> "dcba"),
> here's an example that can be used to reverse a string up to 8 bytes long:
>
> CREATE FUNCTION REVERSE (INC VARCHAR(8))
>
> RETURNS VARCHAR(8)
>
> LANGUAGE SQL
>
> CONTAINS SQL
>
> NO EXTERNAL ACTION
>
> DETERMINISTIC
>
> RETURN
>
> CAST( TRANSLATE(‘87654321’, INC, ’12345678’) AS VARCHAR(8) ) ;
>
> Example:
>
> select reverse('abcde') from sysibm.sysdummy1;
>
> edcba
>
>
>
> If you need to handle longer strings (up to 255) you may use
> x'00010203 ... FCFDFE' :-) instead of '123...'
>
> The good side - it works even on V7, no loop in handling (just one
> assembler TR command).
> The bad side - not fit for those 4k+ strings.
>
>
> Isaac Yassin
> IBM Gold Consultant
> IBM Champion - Information Management
> IBM Certified Solution Expert
> IBM Certified System Administrator - DB2 10 for z/OS
> IBM Certified Database Administrator - DB2 for z/OS V7, V8, 9 & 10
> IDUG Israel RUG
>
>
>
> -----End Original Message-----

Peter Vanroose

RE: REVERSE a string by using TRANSLATE (SQL)
(in response to Isaac Yassin)



In Reply to Isaac Yassin:

CAST( TRANSLATE(‘87654321’, INC, ’12345678’) AS VARCHAR(8) )


This should actually be:

         CAST( LTRIM( TRANSLATE(‘87654321’, INC, ’12345678’) )  AS VARCHAR(8) )

Otherwise the result will be prepended with 8-n blanks (if INC has length n).

(I'm of course assuming here that INC has no trailing blanks...)

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Isaac Yassin

REVERSE a string by using TRANSLATE (SQL)
(in response to Peter Vanroose)
That's why INC is defined as varchar

Isaac Yassin
Sent from my Galaxy S2
forgive typos :(
On Apr 25, 2012 1:16 AM, "Peter Vanroose" <[login to unmask email]>
wrote:

>
>
> In Reply to Isaac Yassin:
>
> CAST( TRANSLATE(‘87654321’, INC, ’12345678’) AS VARCHAR(8) )
>
>
> This should actually be:
>
> CAST( LTRIM( TRANSLATE(‘87654321’, INC, ’12345678’) ) AS
> VARCHAR(8) )
>
> Otherwise the result will be prepended with 8-n blanks (if INC has length
> n).
>
> (I'm of course assuming here that INC has no trailing blanks...)
>
> -- Peter Vanroose
> *ABIS Training & Consulting,*
> * Leuven, Belgium.*
> http://www.abis.be/ <http://www.abis.be/html/enindex.html>
>
> -----End Original Message-----
>

Peter Vanroose

RE: REVERSE a string by using TRANSLATE (SQL)
(in response to Isaac Yassin)

In Reply to Isaac Yassin:

That's why INC is defined as varchar


That doesn't preclude it would end in blanks!

Anyhow, the LTRIM will be necessary (with or without INC having blanks at the end) to avoid blanks at the beginning of the result.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Isaac Yassin

REVERSE a string by using TRANSLATE (SQL)
(in response to Peter Vanroose)
It's your call whether you want the blanks or not. They can be removed
either by using strip on the call or on the result. Forcing it inside the
function does not give you the option go have them if you do want them.
Eg. reverse(strip(.....)) or ltrim(revrrse(....)).

As you have the source - feel free to tweak it to your needs ;-)

Isaac Yassin
Sent from my Galaxy S2
forgive typos :(
On Apr 25, 2012 9:32 AM, "Peter Vanroose" <[login to unmask email]>
wrote:

> In Reply to Isaac Yassin:
>
> That's why INC is defined as varchar
>
>
> That doesn't preclude it would end in blanks!
>
> Anyhow, the LTRIM will be necessary (with or without INC having blanks at
> the end) to avoid blanks at the beginning of the result.
>
> -- Peter Vanroose
> *ABIS Training & Consulting,*
> * Leuven, Belgium.*
> http://www.abis.be/ <http://www.abis.be/html/enindex.html>
>
> -----End Original Message-----
>

Peter Vanroose

RE: REVERSE a string by using TRANSLATE (SQL)
(in response to Isaac Yassin)

OK, Isaac, I see your point, but my point is that reverse(strip(.....)) & ltrim(reverse(....)) are NOT returning the same thing:

If you first strip, then reverse, you will STILL see the blanks in front.

Otherwise said, your REVERSE function does not return a string of the same length as that of the argument, which I would expect as a user of the function.

In Reply to Isaac Yassin:

It's your call whether you want the blanks or not. They can be removed
either by using strip on the call or on the result. Forcing it inside the
function does not give you the option go have them if you do want them.
Eg. reverse(strip(.....)) or ltrim(reverse(....)).

As you have the source - feel free to tweak it to your needs ;-)

 

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Isaac Yassin

REVERSE a string by using TRANSLATE (SQL)
(in response to Peter Vanroose)
Hi Peter,
1. If you first strip then the blanks at end are gone so they will not
appear at front which is what you want.
2. If you do not strip and just use it as written - you get the same length
back.
3. I would not remove the blanks inside the function as I leave it to
users' decision whether they want it or not.
4. The whole idea was to reverse a string, without programming loop, but
not to do any other manipulation. If you want other manipulations, feel
free to add it to your version.


Isaac Yassin



On 27 April 2012 11:04, Peter Vanroose <[login to unmask email]> wrote:

> OK, Isaac, I see your point, but my point is that reverse(strip(.....)) &
> ltrim(reverse(....)) are NOT returning the same thing:
>
> If you first strip, then reverse, you will STILL see the blanks in front.
>
> Otherwise said, your REVERSE function does not return a string of the same
> length as that of the argument, which I would expect as a user of the
> function.
>
>
> In Reply to Isaac Yassin:
>
> It's your call whether you want the blanks or not. They can be removed
>
> either by using strip on the call or on the result. Forcing it inside the
> function does not give you the option go have them if you do want them.
> Eg. reverse(strip(.....)) or ltrim(reverse(....)).
>
>
> As you have the source - feel free to tweak it to your needs ;-)
>
>
>
> -- Peter Vanroose
>
> *ABIS Training & Consulting,*
> * Leuven, Belgium.*
> http://www.abis.be/ <http://www.abis.be/html/enindex.html>
>
>
> -----End Original Message-----
>

Peter Vanroose

RE: REVERSE a string by using TRANSLATE (SQL)
(in response to Isaac Yassin)

In Reply to Isaac Yassin:

  1. If you first strip then the blanks at end are gone so they will not appear at front which is what you want.

 

Sorry, Isaac, but I don't think this is true: with your length-8 version (having the '12345678' and '87654321'), when you pass in the varchar 'ABIS' (length 4, no blank padding) it returns '    SIBA' (four blanks in front).

At least, that's what my version of DB2 on z/OS does...

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Isaac Yassin

REVERSE a string by using TRANSLATE (SQL)
(in response to Peter Vanroose)
Hi Peter,

I went through the cases that I use and they are in the exact needed
length so I did not encounter what you did.
As I also need to have blanks sometimes I can not use Ltrim inside, but
this may work:
return
cast(translate(left('87654321',length(inc)) , inc ,
right('12345678',length(inc)))
as varchar(8) )

Isaac Yassin




On 27 April 2012 11:04, Peter Vanroose <[login to unmask email]> wrote:

> OK, Isaac, I see your point, but my point is that reverse(strip(.....)) &
> ltrim(reverse(....)) are NOT returning the same thing:
>
> If you first strip, then reverse, you will STILL see the blanks in front.
>
> Otherwise said, your REVERSE function does not return a string of the same
> length as that of the argument, which I would expect as a user of the
> function.
>
>
> In Reply to Isaac Yassin:
>
> It's your call whether you want the blanks or not. They can be removed
>
> either by using strip on the call or on the result. Forcing it inside the
> function does not give you the option go have them if you do want them.
> Eg. reverse(strip(.....)) or ltrim(reverse(....)).
>
>
> As you have the source - feel free to tweak it to your needs ;-)
>
>
>
> -- Peter Vanroose
>
> *ABIS Training & Consulting,*
> * Leuven, Belgium.*
> http://www.abis.be/ <http://www.abis.be/html/enindex.html>
>
>
> -----End Original Message-----
>

Peter Vanroose

RE: REVERSE a string by using TRANSLATE (SQL)
(in response to Isaac Yassin)

Yes, perfect! Thanks!

In Reply to Isaac Yassin:

return
cast(translate(left('87654321',length(inc)) , inc ,
right('12345678',length(inc)))
as varchar(8) )


--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Peter Vanroose

RE: REVERSE a string by using TRANSLATE (SQL)
(in response to Isaac Yassin)

Also on behalf of Isaac (after private email conversation, and based on his initial version), here is the fully working (and fully tested) version for reversing text up to 255 characters :

[Works on both z/OS and LUW.]

CREATE FUNCTION reverse (inc VARCHAR(255))
 RETURNS VARCHAR(255)
 LANGUAGE SQL
 CONTAINS SQL
 NO EXTERNAL ACTION
 DETERMINISTIC
 RETURN CAST(TRANSLATE(LEFT(
  X'FEFDFCFBFAF9F8F7F6F5F4F3F2F1F0'||
X'EFEEEDECEBEAE9E8E7E6E5E4E3E2E1E0'||
X'DFDEDDDCDBDAD9D8D7D6D5D4D3D2D1D0'||
X'CFCECDCCCBCAC9C8C7C6C5C4C3C2C1C0'||
X'BFBEBDBCBBBAB9B8B7B6B5B4B3B2B1B0'||
X'AFAEADACABAAA9A8A7A6A5A4A3A2A1A0'||
X'9F9E9D9C9B9A99989796959493929190'||
X'8F8E8D8C8B8A89888786858483828180'||
X'7F7E7D7C7B7A79787776757473727170'||
X'6F6E6D6C6B6A69686766656463626160'||
X'5F5E5D5C5B5A59585756555453525150'||
x'4F4E4D4C4B4A49484746454443424140'||
X'3F3E3D3C3B3A39383736353433323130'||
X'2F2E2D2C2B2A29282726252423222120'||
X'1F1E1D1C1B1A19181716151413121110'||
X'0F0E0D0C0B0A09080706050403020100'
                           ,LENGTH(inc))
                      ,inc
                      ,RIGHT(
X'000102030405060708090A0B0C0D0E0F'||
X'101112131415161718191A1B1C1D1E1F'||
X'202122232425262728292A2B2C2D2E2F'||
X'303132333435363738393A3B3C3D3E3F'||
X'404142434445464748494A4B4C4D4E4F'||
X'505152535455565758595A5B5C5D5E5F'||
X'606162636465666768696A6B6C6D6E6F'||
X'707172737475767778797A7B7C7D7E7F'||
X'808182838485868788898A8B8C8D8E8F'||
X'909192939495969798999A9B9C9D9E9F'||
X'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF'||
x'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'||
X'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF'||
X'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'||
X'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF'||
X'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFE'
                            ,LENGTH(inc)))
             AS VARCHAR(255) )
;

Test with a maximal-length input (with "|" appended and prepended to visually guarantee absence of unwanted blanks):

SELECT '|'||reverse('abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz 0123456789 .')||'|'
FROM sysibm.sysdummy1
;
-- returns: |. 9876543210 zyxwvutsrqponmlkjihgfedcba ZYXWVUTSRQPONMLKJIHGFEDCBA zyxwvutsrqponmlkjihgfedcba ZYXWVUTSRQPONMLKJIHGFEDCBA zyxwvutsrqponmlkjihgfedcba ZYXWVUTSRQPONMLKJIHGFEDCBA zyxwvutsrqponmlkjihgfedcba ZYXWVUTSRQPONMLKJIHGFEDCBA zyxwvutsrqponmlkjihgfedcba|

(So no spurious blanks, no mapping errors.)

The CREATE FUNCTION layout is SPUFI-friendly, the test isn't, sorry. (Tested through DDF.)

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Edited By:
Peter Vanroose[Organization Members] @ May 06, 2012 - 06:41 PM (Europe/Brussels)

Walter Jani&#223;en

AW: REVERSE a string by using TRANSLATE (SQL)
(in response to Peter Vanroose)
Peter

I just created this function and it works great!

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996



________________________________
Von: Peter Vanroose [mailto:[login to unmask email]
Gesendet: Sonntag, 6. Mai 2012 18:20
An: [login to unmask email]
Betreff: [DB2-L] - RE: REVERSE a string by using TRANSLATE (SQL)


Also on behalf of Isaac (after private email conversation, and based on his initial version), here is the fully working (and fully tested) version for reversing text up to 255 characters :

CREATE FUNCTION reverse (inc VARCHAR(255))
RETURNS VARCHAR(255)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN CAST(TRANSLATE(LEFT(
X'FEFDFCFBFAF9F8F7F6F5F4F3F2F1F0'||
X'EFEEEDECEBEAE9E8E7E6E5E4E3E2E1E0'||
X'DFDEDDDCDBDAD9D8D7D6D5D4D3D2D1D0'||
X'CFCECDCCCBCAC9C8C7C6C5C4C3C2C1C0'||
X'BFBEBDBCBBBAB9B8B7B6B5B4B3B2B1B0'||
X'AFAEADACABAAA9A8A7A6A5A4A3A2A1A0'||
X'9F9E9D9C9B9A99989796959493929190'||
X'8F8E8D8C8B8A89888786858483828180'||
X'7F7E7D7C7B7A79787776757473727170'||
X'6F6E6D6C6B6A69686766656463626160'||
X'5F5E5D5C5B5A59585756555453525150'||
x'4F4E4D4C4B4A49484746454443424140'||
X'3F3E3D3C3B3A39383736353433323130'||
X'2F2E2D2C2B2A29282726252423222120'||
X'1F1E1D1C1B1A19181716151413121110'||
X'0F0E0D0C0B0A09080706050403020100'
,LENGTH(inc))
,inc
,RIGHT(
X'000102030405060708090A0B0C0D0E0F'||
X'101112131415161718191A1B1C1D1E1F'||
X'202122232425262728292A2B2C2D2E2F'||
X'303132333435363738393A3B3C3D3E3F'||
X'404142434445464748494A4B4C4D4E4F'||
X'505152535455565758595A5B5C5D5E5F'||
X'606162636465666768696A6B6C6D6E6F'||
X'707172737475767778797A7B7C7D7E7F'||
X'808182838485868788898A8B8C8D8E8F'||
X'909192939495969798999A9B9C9D9E9F'||
X'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF'||
x'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'||
X'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF'||
X'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'||
X'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF'||
X'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFE'
,LENGTH(inc)))
AS VARCHAR(255) )
;

Test with a maximal-length input (with "|" appended and prepended to visually guarantee absence of unwanted blanks):

SELECT '|'||reverse('abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz 0123456789 .')||'|'
FROM sysibm.sysdummy1
;
-- returns: |. 9876543210 zyxwvutsrqponmlkjihgfedcba ZYXWVUTSRQPONMLKJIHGFEDCBA zyxwvutsrqponmlkjihgfedcba ZYXWVUTSRQPONMLKJIHGFEDCBA zyxwvutsrqponmlkjihgfedcba ZYXWVUTSRQPONMLKJIHGFEDCBA zyxwvutsrqponmlkjihgfedcba ZYXWVUTSRQPONMLKJIHGFEDCBA zyxwvutsrqponmlkjihgfedcba|

(So no spurious blanks, no mapping errors.)

The CREATE FUNCTION layout is SPUFI-friendly, the test isn't, sorry. (Tested through DDF.)

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/<http://www.abis.be/html/enindex.html>

-----End Original Message-----