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 of TRANSLATE. |
| Syntax |
TRANSLATE(
str1 IN VARCHAR2 CHARACTER SET ANY_CS,
src IN VARCHAR2 CHARACTER SET STR1%CHARSET,
dest IN VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN IN VARCHAR2 CHARACTER SET STR1%CHARSET; |
| |
| Translate Built-in String Function |
Single Character Replacement
Replace all commas with vertical bars |
SELECT TRANSLATE ('comma,delimited,list', ',',
'|
')
FROM dual; |
Multiple Character Replacement
Turns a DNA sequence into its complement |
SELECT TRANSLATE('CAG-TTT-GAC-ACA-TGG-ATC', 'ACGT
', 'GATC') DNA
FROM dual; |
Character Replacement and Elimination
The 'a' is replaced with an 'e', the 'h' has no complement and is dropped. |
SELECT TRANSLATE('So What', 'ah
', 'e')
FROM dual; |
| Eliminating Double Quotes. Capital A is replaced with capital A. The double quote is eliminated because there is no match. |
SELECT TRANSLATE('"Darn double quotes
"', 'A"', 'A')
FROM dual; |
| Encryption / Decryption |
SELECT TRANSLATE('this is a secret',
'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv')
FROM dual;
SELECT TRANSLATE('p78o 8o 0 o42i4p',
'0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz')
FROM dual; |
| Counting Vowels |
WITH data AS (SELECT 'Whose line is it anyway' line FROM dual)
SELECT LENGTH(line)-LENGTH(TRANSLATE(line,'xaeiou','x')) nbVowels
FROM data; |
| |
| Replace Built-in String Function |
REPLACE
Overload 1 |
REPLACE(
srcstr IN VARCHAR2 CHARACTER SET ANY_CS,
oldsub IN VARCHAR2 CHARACTER SET SRCSTR%CHARSET,
newsub IN VARCHAR2 CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN VARCHAR2 CHARACTER SET SRCSTR%CHARSET; |
REPLACE
Overload 2 |
REPLACE(
srcstr IN CLOB CHARACTER SET ANY_CS,
oldsub IN CLOB CHARACTER SET SRCSTR%CHARSET,
newsub IN CLOB CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN IN CLOB CHARACTER SET SRCSTR%CHARSET; |
| Single Character Replacement |
REPLACE(<string>, <'string_to_match'>,<'replacements_string'>) |
| SELECT REPLACE('So What', 'o', 'ay')
FROM dual; |
| Multiple Character Replacement |
Replacement of a single character with a phrase |
SELECT REPLACE('An idea
that is not dangerous is unworthy of being called a n idea at all.', 'n idea',
' software program') TRUTH
FROM dual; |
