Replace Function in SQL

REPLACE

• This will replace the set of characters, string by string.
• To replace multiple character at a time.

Syntax: replace (string, old_chars [, new_chars])

Ex:

select replace('india','in','xy'), replace('india','in') from dual;

output

replace('india','in','xy')
--------------------------
xydia

replace(‘india’,’in’)
------------------------
dia

some more example

select replace('1oraclesql23','123','456') from dual;
------------------------------------------------------
1oraclesql23

in case of NULL

select replace('ABCD','AB','NULL') from dual;
---------------------------------------------------
NULLCD

select replace('ABCD','AB',NULL) from dual;
---------------------------------------------------
CD