Home › Oracle: replace substring or character

Oracle: replace substring or character 

Dr. Xi
Joined:
04/09/2007
Posts:
565

February 18, 2010 20:39:59    Last update: February 18, 2010 20:41:17
Oracle provides two functions for string replacement: REPLACE and TRANSLATE.

REPLACE replaces one substring with another: 
-- Replace John with Bob
select REPLACE('Hello John', 'John', 'Bob') from dual;

-- Match is case sensitive, lower case john won't match
select REPLACE('Hello John', 'john', 'Bob') from dual;


TRANSLATE replaces one character by another, position for position: 
-- rot13 in Oracle SQL
select TRANSLATE('Hello John', 
                 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
                 'NOPQRSTUVWXYZABCDEFGHIJKLMnopqrstuvwxyzabcdefghijklm')
from dual;

-- Remove all question marks
select TRANSLATE('How? are? you?', '.?', '.') from dual;

-- This does not work!
select TRANSLATE('How? are? you?', '?', '') from dual;
[ Comment  | Tags ]
 
Easy email testing with http://www.ximailstop.com