Oracle PL/SQL - PL/SQL TRANSLATE function

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. CHAR and VARCHAR2 Variables Size

Introduction

The TRANSLATE function allow you to transform text by using the specified pattern shown here: 

v_tx:= translate(string, search, replacement);

The TRANSLATE function takes search and replacement strings and creates character-to-character maps:

Demo

SQL>
SQL> declare--   w  w w .j a va2s.c o m
  2      v1_tx VARCHAR2(20):='To be or not to be';
  3      v2_tx VARCHAR2(20);
  4  begin
  5      v2_tx:= translate (v1_tx,'bo ','BO');
  6      DBMS_OUTPUT.put_line(v2_tx);
  7  end;
  8  /
TOBeOrnOttOBe

PL/SQL procedure successfully completed.

SQL>

If you have more characters in the source string than in the replacement string, those characters are removed.

No spaces appear in the result.

With the TRANSLATE function, the third parameter, the replacement characters, can't be NULL or an empty string. Otherwise, the result is always NULL.

Related Topic