Explain the REPLACE and TRANSLATE string functions in PL/SQL

How it works

REPLACE(string, search_string, [replacement_string]) swaps every occurrence of a whole search string with a replacement — omitting the replacement removes the search string entirely. TRANSLATE(string, from_chars, to_chars) instead does character-by-character substitution based on matching position in the from/to lists, making it the right tool for stripping or remapping individual characters (digits, punctuation) rather than a whole substring.

SELECT
  REPLACE('2026-08-24', '-', '/')                          AS reformatted_date,
  TRANSLATE('Order #4521-B', '0123456789', '##########')   AS masked_numbers
FROM dual;
-- reformatted_date => '2026/08/24'
-- masked_numbers   => 'Order #####-B'

Related Questions