Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. Single Row Character Functions
  5. REPLACE

REPLACE

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
Was this article helpful to you? Yes No

How can we help?