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