Oracle TRANSLATE Function

The Oracle TRANSLATE() function returns a string with all occurrences of each character in a string replaced by its corresponding character in another string.

The TRANSLATE() function allows you to make several single-character, one-to-one translations or substitutions in one operation.

Syntax #

The following illustrates the syntax of the Oracle TRANSLATE() function:

TRANSLATE(string, from_string, to_string)Code language: SQL (Structured Query Language) (sql)

Arguments #

The TRANSLATE() function accepts three arguments:

1) string

is the string that to be translated.

2) from_string

is a string that contains characters that should be replaced.

3) to_string

is a string that matches from_string argument by type and length.

The from_string argument can have more characters than  to_string argument. In this case, the extra characters at the end of  from_string have no corresponding characters in to_string. If these extra characters appear in the input string, then the TRANSLATE() function removes them from the result string.

Return Value #

The TRANSLATE() function returns NULL if any argument is NULL.

Basic Oracle TRANSLATE() function examples #

The following statement uses the TRANSLATE() function to replace semicolons (;) with commas (,):

SELECT
  TRANSLATE('oracle, plsql, database', ';', ',') result
FROM dual;Code language: JavaScript (javascript)

Try it

Output:

'oracle, plsql, database'Code language: JavaScript (javascript)

The following statement uses the TRANSLATE() function to replace square [] and curly braces {} with parentheses ():

SELECT
  TRANSLATE('5*[2+6]/{9-3}', '[]{}', '()()')
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Try it

Here is the result:

'5*(2+6)/(9-3)'Code language: SQL (Structured Query Language) (sql)

Converting GeoJSON points into WKT #

GeoJSON is a standard format for encoding various geographic data structures.

WKT stands for Well-known text which is a text markup language for representing vector geometry objects on a map, spatial reference systems of spatial objects, and transformations between spatial reference systems.

You can use the TRANSLATE() function to convert GeoJSON points to WKT format and vice versa as follows:

SELECT
  TRANSLATE( '[127.8, 75.6]', '[,]', '( )' ) Point, 
  TRANSLATE( '(127.8 75.6)', '( )', '[,]' ) Coordinates
FROM
  dual; 
Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Summary #

• Use the Oracle TRANSLATE() function to perform single-character, one-to-one substitutions in one operation.