Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function

SQL REPLACE, TRANSLATE Function


SQL REPLACE Function

SQL REPLACE function return the string/char of every matched string replace with new string.

Supported Oracle SQL Version :

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Syntax :

REPLACE(string, match_string, replace_string)

Parameters :

  • string is original string.
  • match_string is find string from original string.
  • replace_string is if match_string found replace to replace_string string.

Example :

Consider following example return the every matched string replace with new string.

SQL> SELECT REPLACE('Opal Kole','K','Smo') "REPLACE" FROM DUAL;

REPLACE
-----------
Opal Smoole


SQL TRANSLATE Function

SQL TRANSLATE function return the string/char of every matched character replace with new character. TRANSLATE function replace individual character at a time.

Supported Oracle SQL Version :

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Syntax :

TRANSLATE(string, match_char, replace_char) 

Parameters :

  • string is original string.
  • match_char is find string from original string.
  • replace_char is if match_char character found, replace to replace_char character.

Example :

Consider following example return the every matched character replace with new character.

SQL> SELECT REPLACE('Opal Kole','K','D') "TRANSLATE" FROM DUAL;

TRANSLATE
---------
Opal Dole