A question re: TRANSLATE from player

I received this email last week:

Steven, would you mind giving me a explanation about how TRANSLATE works. The two blocks do not seem to work in a consistent way:

DECLARE
   my_formatted_number   VARCHAR2 (100) := '00001233.056';
BEGIN
   DBMS_OUTPUT.put_line (TRANSLATE (my_formatted_number, 'A0', 'A'));
END;
 
Output: 1233.56

DECLARE
   my_formatted_number   VARCHAR2 (100) := 'Example';
BEGIN
   DBMS_OUTPUT.put_line (TRANSLATE (my_formatted_number, 'Ee', '98'));
END;

Output: 9xampl8.

I am glad that this player took the time to ask about this, because it reminded me that I had not provided all the information I could have in the quiz in which this topic came up (19 August). So I will answer the question here and then add more content to that question.

You see, what I did with that TRANSLATE (my_formatted_number, 'A0', 'A') is a bit of a "trick." This is the header of TRANSLATE:

  function TRANSLATE(STR1 VARCHAR2 CHARACTER SET ANY_CS,
                     SRC VARCHAR2 CHARACTER SET STR1%CHARSET,
                     DEST VARCHAR2 CHARACTER SET STR1%CHARSET)
        return VARCHAR2 CHARACTER SET STR1%CHARSET;

TRANSLATE replaces every occurrence in STR1 of the character in position N of SRC with the corresponding character in position N of DEST.

Now suppose that I want to replace a character with nothing - get rid of that character from the string - say, remove all zeros. I might think to do something like this:

TRANSLATE (my_formatted_number, '0', NULL)

The problem with doing this is that if any of the arguments passed to TRANSLATE is NULL, then TRANSLATE always returns NULL.

So you have to play a little game: change the SRC argument value to contain two letters. The first character can be anything you want, the second chraacter is the one you want removed. Then pass just the first character as DEST, as in:

TRANSLATE (my_formatted_number, 'A0', 'A')

What I am now saying is: change A to A and change 0 to NULL.

And that is why the code you showed me above works as it does.

Regards, SF