Note:Translate and replace are very similar in their appearance but can produce very different results. Translate replaces by position,the first character of the list to match is replaced by the first character of the replacement list. The second character with the second,and if there are characters in the list to match that do not have positional equivalents in the replacements list they are dropped.
Replace replaces the string to match with the replacement string. The replacement of a single character is the same as that ofTRANSLATE. |
| Syntax |
TRANSLATE(
str1VARCHAR2CHARACTER SET ANY_CS,
srcVARCHAR2CHARACTER SET STR1%CHARSET,
destVARCHAR2CHARACTER SET STR1%CHARSET)
RETURNVARCHAR2CHARACTER SET STR1%CHARSET; |
|
| Translate Built-in String Function |
Single Character
Replacement |
TRANSLATE(<string>,<'list_to_match'>,<'replacements_list'>)
This demo replaces all commas with vertical bars. |
SELECTTRANSLATE('comma,delimited,list',',','|')
FROMDUAL; |
Multiple Character
Replacement |
The following takes a DNA sequence and returns its complement |
SELECTTRANSLATE('CAG-TTT-GAC-ACA-TGG-ATC','ACGT','GATC') DNA
FROMDUAL; |
Character Replacement
And Elimination |
The a is replaced with an e,the h has no complement and is dropped. |
SELECTTRANSLATE('So What','ah','e')
FROMDUAL; |
Eliminating Double
Quotes |
Capital A is replaced with capital A. The double quote is eliminated because there is no match. |
SELECTTRANSLATE('"Darn double quotes"','A"','A')
FROMDUAL; |
| Encryption / Decryption |
In this demo a string is first encrypted then decrypted |
SELECTTRANSLATE('this is a secret',
'abcdefghijklmnopqrstuvxyz','0123456789qwertyuiop[kjhbv')
FROMDUAL;
SELECTTRANSLATE('p78o 8o 0 o42i4p',
'0123456789qwertyuiop[kjhbv','abcdefghijklmnopqrstuvxyz')
FROMDUAL; |
| Counting Vowels |
In this demo the number of vowels in the string is counted |
WITH dataAS(SELECT'Whose line is it anyway' lineFROMDUAL)
SELECTLENGTH(line)-LENGTH(TRANSLATE(line,'xaeIoU','x')) nbVowels
FROMdata; |
|
| Replace Built-in String Function |
| REPLACE (overload 1) |
REPLACE(
srcstrVARCHAR2CHARACTER SET ANY_CS,
oldsubVARCHAR2CHARACTER SET SRCSTR%CHARSET,
newsubVARCHAR2CHARACTER SET SRCSTR%CHARSET := NULL)
RETURNVARCHAR2CHARACTER SET SRCSTR%CHARSET; |
| REPLACE (overload 2) |
REPLACE(
srcstrCLOBCHARACTER SET ANY_CS,
oldsubCLOBCHARACTER SET SRCSTR%CHARSET,
newsubCLOBCHARACTER SET SRCSTR%CHARSET := NULL)
RETURNCLOBCHARACTER SET SRCSTR%CHARSET; |
Single Character
Replacement |
REPLACE(<string>,<'string_to_match'>,<'replacements_string'>) |
SELECTREPLACE('So What','o','ay')
FROMDUAL; |
Multiple Character
Replacement |
Replacement of a single character with a phrase |
SELECTREPLACE('An ideathat is not dangerous is unworthy of being called an ideaat all.','n idea','software program') TRUTH
FROMDUAL; |
