Re: TRANSLATE function doesn't work correctly?

From: Ranganathan Chakravarthi <ranga_at_shell.one.net>
Date: 1997/06/18
Message-ID: <5o8hqh$653$1@news.one.net>#1/1

Michael Carmack (mcarmack_at_freenet.columbus.oh.us) wrote:
:
: I'm scratching my head over what seems to be buggy behavior in the
: TRANSLATE function in Oracle 7.1.5. I'm using it to implement some simple
: encryption, but the decrypting doesn't seem to always work correctly.
: Here's what I mean:
:
: Here's a sample encryption statement:
:
: 1 select TRANSLATE(
: 2 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
: 3 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
: 4 '3EOX4KA7NVFJWPCS2GTH158DRLQYMGB9ZU0I'
: 5 ) "Encrypted String"
: 6* from dual
: SQL> /
:
: Encrypted String
: ------------------------------------
: 3EOX4KA7NVFJWPCS2GTH158DRLQYMGB9ZU0I
:
: The result is exactly what it should be. But if I turn around and try to
: decrypt that result back into its original form, there is an anomoly:
:
: 1 select TRANSLATE(
: 2 '3EOX4KA7NVFJWPCS2GTH158DRLQYMGB9ZU0I',
: 3 '3EOX4KA7NVFJWPCS2GTH158DRLQYMGB9ZU0I',
: 4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
: 5 ) "Decrypted String"
: 6* from dual
: SQL> /
:
: Decrypted String
: ------------------------------------
: ABCDEFGHIJKLMNOPQ3STUVWXYZ0123456789
: ^
: ^
:
: As you can see, the letter 'R' has not been correctly decrypted, i.e. the
: character '2' is not being translated back into an 'R' as specified in the
: statement.
:
: Has anyone else experienced this odd behavior? Is there a known
: workaround (besides upgrading to 7.3)?
: --
: ========================================================================
: "Villains, I say to you now: | Mike Carmack
: KNOCK OFF ALL THAT EVIL!" | Vulcan Dragon -==(UDIC)==-
: S P O O N !!!! - The Tick | mcarmack_at_freenet.columbus.oh.us

select TRANSLATE(

   'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
   'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
   '3EOX4KA7NVFJWPCS2GTH158DRLQYMGB9ZU0I'
        ) "Encrypted String"

from dual

I think, the space before the third parameter to translate, confused you. Also, note that you have two charactares that translates to G (R and 3). Actually Q translates to 2 and not R. In your translate character you left out the number 6. If you use 6 for R you should be okay.

1 select TRANSLATE(

  2       'ABCDEFGHIJKLMNOPQRSTVUWXYZ0123456789',
  3       'ABCDEFGHIJKLMNOPQRSTVUWXYZ0123456789',
  4       '3EOX4KA7NVFJWPCS26TH518DRLQYMGB9ZU0I'
  5           ) "encrypted string"

6* from dual

encrypted string

3EOX4KA7NVFJWPCS26TH518DRLQYMGB9ZU0I 1 select TRANSLATE(

  2       '3EOX4KA7NVFJWPCS26TH518DRLQYMGB9ZU0I',
  3       '3EOX4KA7NVFJWPCS26TH518DRLQYMGB9ZU0I',
  4       'ABCDEFGHIJKLMNOPQRSTVUWXYZ0123456789'
  5           ) "decrypted string"

6* from dual
SQL> / decrypted string

ABCDEFGHIJKLMNOPQRSTVUWXYZ0123456789 Oops! Swapped the 'U' and V'.

HTH!

Ranga
life, n.: That brief interlude between nothingness and eternity.

Received on Wed Jun 18 1997 - 00:00:00 CDT