CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Translate & Replace
Version 11.1
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 VARCHAR2 CHARACTER SET ANY_CS,
src  VARCHAR2 CHARACTER SET STR1%CHARSET,
dest VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER 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.
SELECT TRANSLATE('comma,delimited,list', ',', '|')
FROM DUAL;
Multiple Character
Replacement
The following takes a DNA sequence and returns 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 In this demo a string is first encrypted then decrypted
SELECT TRANSLATE('this is a secret',
'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv')
FROM DUAL;

SELECT TRANSLATE('p78o 8o 0 o42i4p',
'0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz')
FROM DUAL;
Counting Vowels In this demo the number of vowels in the string is counted
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 VARCHAR2 CHARACTER SET ANY_CS,
oldsub VARCHAR2 CHARACTER SET SRCSTR%CHARSET,
newsub VARCHAR2 CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN VARCHAR2 CHARACTER SET SRCSTR%CHARSET;
REPLACE (overload 2) REPLACE(
srcstr CLOB CHARACTER SET ANY_CS,
oldsub CLOB CHARACTER SET SRCSTR%CHARSET,
newsub CLOB CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN 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 an idea at all.', 'n idea', ' software program') TRUTH
FROM DUAL;
 
Related Topics
Built-in Functions
String Functions
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [276 users online]    © 2010 psoug.org