Oracle String/Char Function - Oracle/PLSQL TRANSLATE Function

This Oracle tutorial explains how to use the Oracle/PLSQL TRANSLATE function.

TRANSLATE(x,from_string, to_string) converts all occurrences of from_string in x to to_string letter by letter.

TRANSLATE replaces a string character by character. REPLACE does the whole string seaching and replacing. TRANSLATE matches characters by character and replace the string character by character. The general format for this function is:

TRANSLATE(string, characters_to_find, characters_to_replace_by)


SQL> SELECT TRANSLATE('This is a test', 's','S') FROM dual;

TRANSLATE('THI
--------------
ThiS iS a teSt

SQL>

Syntax

The syntax for the Oracle/PLSQL TRANSLATE function is:

TRANSLATE( string1, string_to_replace, replacement_string )

string1 is the string to replace.

string_to_replace is the string that will be searched for in string1.

replacement_string - All characters in the string_to_replace will be replaced with the character in the replacement_string.

Example


SQL> select translate('123456', '123', '456') from dual;
-- ww w. java2s. c o  m
TRANSL
------
456456

SQL> select translate('asdfasdf123', '2ec', '3it') from dual;

TRANSLATE('
-----------
asdfasdf133

SQL>

Convert numbers

Use TRANSLATE() to convert numbers.


SQL> SELECT TRANSLATE(12345,54321,67890) FROM dual;

TRANS
-----
09876

SQL>

Encoding a string

The following code shows how to use translate function to encode a string:


SQL> SELECT TRANSLATE('THIS iS a TEST',
  2  'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
  3  'EFGHIJKLMNOPQRSTUVWXYZABCD')
  4  FROM dual;-- from www.j av  a2s .  co m

TRANSLATE('THI
--------------
XLMW iW a XIWX

SQL>

Pass column values to `TRANSLATE()`


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from  w  w  w  .  j a  v a  2s  . c o  m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);


SQL> SELECT ename, TRANSLATE(ename,
  2  'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
  3  'EFGHIJKLMNOPQRSTUVWXYZABCDefghijklmnopqrstuvwxyzabcd')
  4  FROM emp;

ENAME      TRANSLATE(
---------- ----------
SMITH      WQMXL
ALLEN      EPPIR
WARD       AEVH
JONES      NSRIW
MARTIN     QEVXMR
BLAKE      FPEOI
CLARK      GPEVO
SCOTT      WGSXX
KING       OMRK
TURNER     XYVRIV
ADAMS      EHEQW

11 rows selected.

SQL>
SQL>
SQL>
SQL>