Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 3 Oracle Functions
  5. Character Functions: REPLACE, TRANSLATE

Character Functions: REPLACE, TRANSLATE

REPLACE function in Oracle

SELECT REPLACE('ORACLE BOOK', 'O', 'E') str_replace 
  FROM DUAL;

STR_REPLACE
------------
ERACLE BEEK
SELECT REPLACE('ORACLE BOOK', 'O', 'EEE') str_replace
  FROM DUAL;

STR_REPLACE
-----------------
EEERACLE BEEEEEEK
SELECT REPLACE('ORACLE BOOK', 'OA', 'EEE') str_replace
  FROM DUAL;

STR_REPLACE
------------
ORACLE BOOK
SELECT REPLACE('ORACLE BOOK', 'O') str_replace
  FROM DUAL;

STR_REPLACE
------------
RACLE BK
SELECT REPLACE('ORACLE BOOK', 'MA', 'PIN') str_replace
   FROM DUAL;

STR_REPLACE
-------------
PINACLE BOOK
SELECT REPLACE('ORACLE BOOK', 'ORACLE', 'PLS') str_replace
  FROM DUAL;

STR_REPLACE
-------------
PLS BOOK

Q : How to get the number of O’s in the string ‘THE ULTIMATE CODEMAN’?

Ans:

SELECT 
    'THE ULTIMATE CODEMAN' Original,
    LENGTH('THE ULTIMATE CODEMAN') - LENGTH(REPLACE('THE ULTIMATE CODEMAN', 'O')) "No Of O's"
  FROM DUAL;

While operating with any character function, we should keep in mind that characters are case sensitive, else few queries will produce undesired output confusing in many situations.

Q : How to get the number of O’s in the string ‘Oracle Organization’?

Ans:

SELECT 'Oracle Organization' Original,
 LENGTH('Oracle Organization') - LENGTH(REPLACE('Oracle Organization', 'O')) "No Of O's"
  FROM DUAL;

ORIGINAL               No Of O's
--------------------   ---------
Oracle Organization            2
SELECT 'Oracle Organization' Original,
 LENGTH('Oracle Organization') - LENGTH(REPLACE(UPPER('Oracle Organization'), 'O')) "No Of O's"
 FROM DUAL;

ORIGINAL               No Of O's
--------------------   ---------
Oracle Organization            3


TRANSLATE function in Oracle

SELECT TRANSLATE('EDUCATION', 'E', 'U') FROM DUAL;

TRANSLATE
----------
UDUCATION
SELECT TRANSLATE('EDUCATION', 'E', 'OO') FROM DUAL;

TRANSLATE
----------
ODUCATION
SELECT TRANSLATE('EDUCATION', 'AEIU', 'OOOO') FROM DUAL;

TRANSLATE
----------
ODOCOTOON
SELECT TRANSLATE('EDUCATION', 'AIU', 'OOOO') FROM DUAL;

TRANSLATE
----------
EDOCOTOON
SELECT TRANSLATE('EDUCATION', 'E') FROM DUAL;
 SELECT TRANSLATE('EDUCATION', 'E') FROM DUAL
        *
 ERROR at line 1:
 ORA-00909: invalid number of arguments
SELECT TRANSLATE('EDUCATION', 'E', NULL) FROM DUAL;

T
-
 
SELECT TRANSLATE('EDUCATION', 'E', ' ') FROM DUAL;

TRANSLATE
----------
 DUCATION
SELECT TRANSLATE('EDUCATION', 'EU', 'O') FROM DUAL;

TRANSLATE
----------
ODCATION
SELECT TRANSLATE('EDUCATION', 'AEIOU', 'E') FROM DUAL;

TRANSLATE
----------
DCETN
SELECT TRANSLATE('EDUCATION') FROM DUAL;
 SELECT TRANSLATE('EDUCATION') FROM DUAL
    *
 ERROR at line 1:
 ORA-00909: invalid number of arguments
SELECT TRANSLATE('THE ULTIMATE CODEMAN', ' ', '') FROM DUAL;

T
-
 
SELECT TRANSLATE('THE ULTIMATE CODEMAN', 'O ', 'O') 
  FROM DUAL;

TRANSLATE('ORACLE
------------------
ORACLECORPORATION

Q : Determine the no. of vowels present in ‘THE ULTIMATE CODEMAN’?

Ans:

SELECT 
    UPPER('THE ULTIMATE CODEMAN') Actual, 
    LENGTH('THE ULTIMATE CODEMAN') - 
       LENGTH(TRANSLATE(UPPER('THE ULTIMATE CODEMAN'),  'BAEIOU', 'B')) Consonant
  FROM DUAL;

Q : How to remove all numbers from a given string?

Ans:

SELECT TRANSLATE('&GiveStr', 'A1234567890', 'A') Str
  FROM DUAL;

Enter value for givestr: THE123CO34DE45678MAN90

STR
-------
THECODEMAN
Was this article helpful to you? Yes No

How can we help?