Oracle Translate & Replace Functions
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose 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.
 
TRANSLATE
Single character replacement that  replaces all commas with vertical bars 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;
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;
Simple encryption and 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
VARCHAR2 demonstrating single character replacement

Overload 1
-- REPLACE(<string>, <'string_to_match'>,<'replacements_string'>)

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;
conn uwclass/uwclass@pdbdev

SELECT REPLACE('So What', 'o', 'ay') FROM dual;
CLOB demonstrating multiple character replacement

Overload 2
REPLACE(<string>, <'string_to_match'>,<'replacements_string'>)

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;
conn uwclass/uwclass@pdbdev

CREATE TABLE reptest (
truth CLOB);

INSERT INTO reptest VALUES ('An idea that is not dangerous is unworthy of being called an idea at all.');
COMMIT;

SELECT * FROM reptest;

SELECT REPLACE(truth, 'n idea', ' software program') TRUTH
FROM reptest;

Related Topics
All Functions
REGEXP_REPLACE
String Functions

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved