OracleBlog

TRANSLATE is a useful little function that can replace given characters in the first string with other given characters. TRANSLATE will go through the provided string looking for any instance of characters in the first list and, when found, replace them with the corresponding character in the second list. Any characters in the given string that don't show up in the first list are left alone.

If the first list is longer than the second list, that means that some characters have no corresponding character. In that case, such characters are simply replaced with nothing (ie. deleted). So if you want to remove a character, put them at the end of your first list. It clearly doesn't make sense for the second list to be longer than the first list, nor does it make sense to have duplicates in the first list (although it certainly makes sense in the second list). If you do have inconsistent duplicates in your first list, Oracle seems to choose the character in the second list corresponding to its first occurence in the first list.

By the way, the two lists are actually passed as strings, but it makes more sense to picture them as lists. C programmers will be most comfortable, because they are used to interpreting strings as arrays of characters, and that's what we're dealing with here.

TRANSLATE is under chapter 6 (Functions) of Oracle's SQL Reference.

Let's look at a first, simple example to remove the dashes in a phone number and replace them with dots to form some kind of standard syntax.

scott@Robert> SELECT TRANSLATE('(619)455-1998', ')-(', '..') PHONE_NO FROM DUAL;

PHONE_NO
------------
619.455.1998

Complex constraints are a particularly good use for TRANSLATE. Let's presume you have a column that represents a special code in your company where the 4th character must be a digit, and the first digit. Well, you may know that INSTR can tell you where the first occurence of a particular character is, but you're looking for 10 characters, how can that be done? Quite easily, because with TRANSLATE you can change all characters to a single one.

CREATE TABLE MyTable (special_code VARCHAR2(32) CHECK (INSTR(TRANSLATE(special_code, '123456789', '000000000'), '0') = 4));

scott@Robert> INSERT INTO MyTable (special_code) VALUES ('abc123');

1 row created.

scott@Robert> INSERT INTO MyTable (special_code) VALUES ('abcd1234');
INSERT INTO MyTable (special_code) VALUES ('abcd1234')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C008454) violated

That is an excellent technique of how TRANSLATE can be applied to a problem. As a side note, we can use RPAD to generate that second list for us if it is very long.

CREATE TABLE MyTable (special_code VARCHAR2(32) CHECK (INSTR(TRANSLATE(special_code, '123456789', RPAD('0', 9, '0')), '0') = 4));

Let's say you have a special column that should ONLY have numbers. How can you do that? Well how about you delete them all, and then see if you have an empty string? That would look something like this:

CREATE TABLE MyTable (numeric_only VARCHAR2(32) CHECK (TRANSLATE(numeric_only, '0123456789', '') IS NULL));

scott@Robert> INSERT INTO MyTable (numeric_only) VALUES ('abc123');

1 row created.

Oops? What happened? I'll show you this little feature of TRANSLATE:

scott@Robert> SELECT TRANSLATE('abc123', '0123456789', '') FROM DUAL;

T
-

The truth is, if the second list is empty, it seems to wipe out the entire string. Fortunately there is a pretty easy way around this. Just make sure the second string isn't empty. Map some non-important character to the same character, like so:

scott@Robert> SELECT TRANSLATE('abc123', '$0123456789', '$') FROM DUAL;

TRA
---
abc

Let's try this new constraint:

CREATE TABLE MyTable (numeric_only VARCHAR2(32) CHECK (TRANSLATE(numeric_only, '$0123456789', '$') IS NULL));

scott@Robert> INSERT INTO MyTable (numeric_only) VALUES ('1234');

1 row created.

scott@Robert> INSERT INTO MyTable (numeric_only) VALUES ('abc123');
INSERT INTO MyTable (numeric_only) VALUES ('abc123')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C008456) violated

Much better! You know, if we wanted to allow some non-numeric characters, but no more than 2, we could use LENGTH to do that.

CREATE TABLE MyTable (max_2 VARCHAR2(32) CHECK (LENGTH(TRANSLATE(max_2, '$0123456789', '$')) <= 2));

scott@Robert> INSERT INTO MyTable (max_2) VALUES ('abc123');
INSERT INTO MyTable (max_2) VALUES ('abc123')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C008459) violated

scott@Robert> INSERT INTO MyTable (max_2) VALUES ('ab123');

1 row created.

As a quick aside, you'll notice that I specify my column names when I'm doing an insert. Why, you may ask, do I do that? Well don't you think what I'm doing is clearer to you, the reader, when I specify which values I'm inserting? Furthermore, if the schema of a table changes, my INSERT command will still work, assuming the schema change doesn't affect the columns I'm using, and that there are no new parameters that require a value.

You can do so much with TRANSLATE when combined with other functions, such as (to name just a few):

INSTR: gets the position of the first matching character
TRIM: take away spaces, or specific, single characters (also see LTRIM, RTRIM)
UPPER: converts all characters to upper case (also see LOWER)
RPAD: creates a string of the same character (also see LPAD)
LENGTH: calculates the length of a string

Despite the simplicity of the function, it comes in useful in complex constraints and transformations, both by itself and in concert with other Oracle functions.

As a final note, for those of you needing something other than the 1-to-1 switch that TRANSLATE allows, and instead needing to replace one substring with another, you want to use REPLACE. Doubtlessly that will be the topic of a later post.

Dan Morgan's Reference on TRANSLATE:
http://www.psoug.org/reference/translate_replace.html