Oracle 19c SQL

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

TRANSLATE

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?