Oracle TRANSLATE

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.

Examples

A) Simple TRANSLATE() function example

The following statement replaces square [] and curly braces {} in an expression string with parentheses ():

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

Here is the result:

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

B) Convert 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)

The following illustrates the result:

In this tutorial, you have learned how to use the Oracle TRANSLATE() function to perform single-character, one-to-one substitutions in one operation.

Was this tutorial helpful?