Oracle/SQL REPLACE Function is used to replace a sequence of characters within a string with another set of characters. Oracle REPLACE Function can be used to substitute a string, a single characters or NULL for a character or set of characters(string). Syntax and examples of SQL REPLACE STRING and SQL REPLACE TEXT are mentioned below.
Oracle/SQL REPLACE Function Syntax
SELECT REPLACE(string_1,string_to_replace[,replacement_string]) FROM table_name;
In the above Oracle REPLACE Function Syntax:-
- string_1 is the string in which replacements will be made
- string_to_replace is the string which will be replaced in string_1
- replacement_string is an optional parameter, if present, then all the occurrences of string_to_replace in string_1will be replaced by replacement_string, if omitted, then all the occurrences of string_to_replace in string_1 will be omitted and the remaining part of string_1 will be returned.
Oracle/SQL REPLACE Function – Replacing with NULL Example
Oracle REPLACE Function is used with the SQL SELECT Statement.
Oracle REPLACE Function can take 2 or 3 arguments.
The first one is the string in which the replacements are to be done.
The second one is the string which is to be replaced.
The third (optional) one is the string which will replace the second string.
For example, the below Oracle REPLACE Function query returns replaces null at places where value is ‘T’.
SELECT REPLACE('Tech Honey', 'T') "PLSQL Replace Function"
FROM dual;
The above Oracle REPLACE Function query returns ‘ech Honey’.
Note: We have aliased REPLACE(‘Tech Honey’, ‘T’) as PLSQL Replace Function.
Oracle/SQL REPLACE Text Example
Oracle/SQL REPLACE TEXT queries can be written to replace a character in string with another string or character.
For example, the SQL REPLACE TEXT query below replaces ‘T’ with ‘t’.
SELECT REPLACE('Tech Honey', 'T','t') "SQL REPLACE TEXT"
FROM dual;
The above REPLACE SQL TEXT query returns ‘tech Honey’
Note:We have aliased SELECT REPLACE(‘Tech Honey’, ‘T’,’t’) as SQL REPLACE TEXT.
Oracle/SQL REPLACE String Example
SQL REPLACE String queries can be written to replace a string with another string or character.
For example, the SQL REPLACE String query below replaces ‘Tech’ with ‘t’.
SELECT REPLACE('Tech Honey', 'Tech','t') "SQL REPLACE String"
FROM dual;
The above REPLACE SQL STRING query returns ‘t Honey’
Note: We have aliased SELECT REPLACE(‘Tech Honey’, ‘Tech’,’t’) as SQL REPLACE String.