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.