Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

TRANSLATE Function

Description

The TRANSLATE function searches for a character specified as a character string, from_substring, within a given character string, string, and replaces it with a character specified as a character string, to_substring, if exists. Correspondence relationship is determined according to the order of characters specified by from_substring and to_substring. All characters in from_substring that do not have a one to one correspondence relationship with the characters in to_substring are removed from the string. The TRANSLATE function is working like the REPLACE function, but you cannot omit the to_substring argument with this function.

Syntax

TRANSLATE( string, from_substring, to_substring )

 

string :

? character string

? NULL

 

from_substring :

? character string

? NULL

 

to_substring :

? character string

? NULL

  • string : Specifies the original string. If the value is NULL, NULL is returned.
  • from_substring : Specifies the string to be retrieved. If the value is NULL, NULL is returned.
  • to_substring : Specifies the character string in the from_substring to be replaced. It cannot be omitted. If the value is NULL, NULL is returned.
Example

--it returns NULL when an argument is specified with NULL value

SELECT TRANSLATE('12345abcdeabcde','abcde', NULL);

 

======================

  NULL

 

--it translates 'a','b','c','d','e' into '1', '2', '3', '4', '5' respectively

SELECT TRANSLATE('12345abcdeabcde', 'abcde', '12345');

 

======================

  '123451234512345'

 

--it translates 'a','b','c' into '1', '2', '3' respectively and removes 'd's and 'e's

SELECT TRANSLATE('12345abcdeabcde','abcde', '123');

 

======================

  '12345123123'

 

--it removes 'a's,'b's,'c's,'d's, and 'e's in the string

SELECT TRANSLATE('12345abcdeabcde','abcde', '');

======================

  '12345'

 

--it only translates 'a','b','c' into '3', '4', '5' respectively

SELECT TRANSLATE('12345abcdeabcde','ABabc', '12345');

 

======================

  '12345345de345de'