How to Replace Multiple Characters in a String in Oracle?

How to Replace Multiple Characters in a String in Oracle?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 2 mins read

In Oracle, you can use replace() function to replace multiple characters in a string.

Replace() Function Syntax

replace(char, search_string, replacement_string)

In its result, REPLACE will replace all instances of the search string with replacement string in char. When the optional replacement string is missing or null, the search string is eradicated from the query. A char is returned if search string is empty.

Replacing Multiple Characters in Oracle Example

The following Oracle SQL query will replace all the occurrences of dollar sign with a white space:

select replace('This$is$a$string.', '$', ' ') 
    from dual;

Output:

This is a string.

To simply remove the '$' sign from the string, no need to provide the replacement character. Below is an example:

select replace('This$is$a$string.', '$') from dual

Output:

Thisisastring.

Using Replace() Function in PL/SQL

The following PL/SQL program will stuff the asterisk (*) wherever it will find the blank space:

declare
  v_string varchar2(100);
begin
  v_string := replace('This is how it works.', ' ', '*');
  dbms_output.put_line(v_string);
end;

Output:

This*is*how*it*works.

See also: