SQL provides an alterative TRANSLATE function which uses a comma
    separated list of arguments.

    TRANSLATE ( <sourcestring>, <fromstring>, <tostring> )

    This format of the TRANSLATE function uses two translation
    character strings to define the translation of specific
    characters. Any characters in the <sourcestring> which do not
    appear in the <fromstring> are not replaced. Any characters
    in the source string which do appear in the <fromstring> are
    replaced with the character from the corresponding position
    in the <tostring>. The <fromstring> may be longer than the
    <tostring> and in this case the matched character is omitted
    from the result.

    If any of the arguments <sourcestring>, <fromstring>, and
    <tostring> are NULL then the result of the TRANSLATE function
    is NULL.

    The data type of the result is a VARCHAR string with a length and
    character set equivalent to that of the <sourcestring>.

    Examples

    Example 1: Eliminating characters

    This example removes the single quote punctuation character
    from names. For example, "O'Hara" will becomes "OHara". This
    would usually be done during sorting to avoid having the single
    quote group these names separately, and instead they are ordered
    between names starting with "Og" (such as Ogdan) and "Oi" (such
    as Oiler).

    SQL> select last_name
    cont> from EMPLOYEES
    cont> order by TRANSLATE (last_name, '"''', '"');

                                   NOTE

       In Oracle RDBMS the empty string is considered to be NULL,
       so an extra '"' character was added to the translation
       strings to avoid a NULL result. This is not required for
       Oracle Rdb. However, if an ORACLE dialect is used then these
       Oracle semantics would be possible.

    Example 2: Formatting characters

    When numeric values are displayed they are normally displayed
    with leading spaces, however, some applications require leading
    zeros. This example assumes that the postal code is stored as an
    INTEGER but needs to report the 5 digits with leading zeros.

    SQL> select TRANSLATE (CAST(postal_code as CHAR(5)), ' ', '0')
    cont> from EMPLOYEES;

    Example 3: Masking characters

    TRANSLATE can be used to mask out characters which should not
    appear in the output. For instance, when displaying a license
    number all the letters are required to be converted to 'X' and
    all digits to '9'.

    SQL> select TRANSLATE ('2KRW229',
    cont>       '01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ',
    cont>       '99999999999XXXXXXXXXXXXXXXXXXXXXXXXXX')
    cont> from ...;
     9XXX999

    Example 4: Transforming text for sorting

    Last names often contain special punctuation characters, such as
    the single quote in O'Sullivan, or D'Amico. Consider this simple
    example from the PERSONNEL database.

    SQL> select last_name
    cont>  from employees
    cont>  where last_name starting with 'D'
    cont>  order by last_name;
     LAST_NAME
     D'Amico
     Dallas
     Danzig
     Dement
     Dement
     Dietrich
     Dietrich
    7 rows selected
    SQL>

    You can see that the quote punctuation causes the name to sort
    higher than expected by many applications (such as telephone book
    listings).

    This example removes the single quote punctuation character from
    names and converts the last name to lowercase so that these names
    sort within similar names without quote punctuation. However, the
    original name is displayed as stored in the column.

    SQL> select last_name
    cont>  from employees
    cont>  where last_name starting with 'D'
    cont>  order by translate (last_name,
    cont>                      'ABCDEFGHIJKLMNOPQRSTUVWXYZ''',
    cont>                      'abcdefghijklmnopqrstuvwxyz');
     LAST_NAME
     Dallas
     D'Amico
     Danzig
     Dement
     Dement
     Dietrich
     Dietrich
    7 rows selected
    SQL>

    The first string argument to TRANSLATE contains the target set
    of characters, if a character in the name does not match this set
    it is written to the result unchanged. For example, the trailing
    spaces are simply copied to the result.

    The second string argument contains the translations for those
    characters. Any upper case characters are transformed into
    their lower case equivalent. If a single quote character is
    matched then it is omitted from the result because there was
    no translation.

    Applying these rules will convert "D'Amico" to "damico" so that
    it sorts between "dallas" and "danzig".