PLSQL Replace

Definition of PLSQL Replace

PL/ SQL REPLACE is a function which allows the replacement of character string with another set of character strings. This function is widely used while programming in case of some spelling mistakes of the data. Instead of deleting the data and interesting again, REPLACE can be used which will modify the data according to the specific requirements. The REPLACE function takes 3 parameters in which one parameter is Optional. It also works great in case of removal of string characters from the particular input string expression. As this function works on the strings, the return type is also a string type but the char set depends on the input string data type.

Syntax:

Below given is the syntax of PL/SQL Replace function:

REPLACE (string1, string_to_replace [, replacement_string])

where,

• string1: The string or expression where the sequence of characters needs to be replaced with other characters. This is basically an input string.
• string_to_replace: The string which will be searched in string1 and will be replaced in the expression.
• replacement_string: It is an Optional parameter. It is a replacement string. All the occurrences of ‘string_to_replace’ will be replaced with ‘replacement_string’. If the replacement_string is not present in the string expression, all the occurrences of ‘replacement_string’ are removed from the string1.
• Return type: The REPLACE function returns the string value to the user.

How does the PLSQL Replace Function Work?

Below given are some of the important points which needs to keep in mind related to the working of REPLACE function in PL/SQL:

1. The REPLACE function of PL/SQL is used to replace a sequence of string with another set of string/ characters.
2. It accepts 3 parameters in which 1 parameter is Optional , i.e. string1( input string), string_to_replace (string to be searched), replacement_string (Optional. replacing string).
3. In case when the string_to_replace( string to be searched) is null, it will return the same string1 (input string expression).
4. All the parameters, i.e. string1, string_to_replace and replacement_string can be of any data type, i.e. CHAR, VARCHAR2, NVARCHAR2, NCHAR, CLOB. The resulting string is of the same character set as char.
5. The REPLACE function of PL/ SQL provides the functionality somewhat similar to that of TRANSLATE function, only difference being that TRANSLATE allows one-to-one substitution whereas REPLACE allows replacement of string/ characters, even removal of character strings (in case of omission of Optional parameter).
6. If the replacement_string is not present in the REPLACE function of PL/ SQL, it works for the removal of string characters. It removes all the occurrences of string_to_replace in the string1. Function returns the removed characters string.

Supported versions Oracle which supports the REPLACE function in PL/ SQL are given below:

• Oracle 8i
• Oracle 9i
• Oracle 10g
• Oracle 11g
• Oracle 12c

Examples of PLSQL Replace

Some of the examples of PL/ SQL showing the implementation of REPLACE function in the code are given below:

Example #1

Code:

DECLARE
Tst_String string(25) := 'hellohello';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'h'));
END;

Output:

Explanation: In the above code, string1 or the string expression is ‘hellohello’. REPLACE function used above has 2 parameters, i.e. ‘Tst_String’ which has a string expression and the string_to_replace is ‘h’. There is no replacement_string in the above REPLACE function. So, first, the string ‘hi’ is searched in the string expression and will be removed from it. So, the resulting string expression is ‘elloello’ removing all the occurences of ‘h’.

Example #2

DECLARE
Tst_String string(25) := 'hello how are you';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'hello', 'hi'));
END;

Output:

Explanation: In the above code, string1 or the string expression is ‘hello how are you’. The REPLACE function used above has 3 parameters ‘Tst_String’ which has a string expression, string_to_replace is ‘hello’ and the replacement_string is ‘hi’. Since ‘hello’ is present 1 time in the expression, it would be replaced with ‘hi’ in the input expression. So, the resulting string expression is ‘hi how are you’ in the above code.

Example #3

Code:

DECLARE
Tst_String string(25) := 'hello how are you';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'are', '  '));
END;

Output:

Explanation: In the above code, string1 or the string expression is ‘hello how are you’. The REPLACE function used above has the input parameter Tst_String (which is string1), string_to_replace is  ‘are’ and the replacement_string is ‘  ‘ (two blank spaces). So in the string expression, ‘are’ is replaced with the blank spaces. So, the resulting string is ‘hello how you’ in the code mentioned above.

Example #4

Code:

DECLARE
Tst_String string(25) := 'hi how are you hi';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'hi'));
END;

Output:

Explanation: In the above code, string1 or the string expression is ‘hi how are you hi’. So in the function ‘REPLACE’, there are 2 parameters, Tst_String which is string1 and the string_to_replace is ‘hi’. There is no replacement_string parameter written above, so ‘hi’ is removed from the expression (nothing would be replaced). So the resulting string is    ‘   how are you   ‘ replacing all the ‘hi’ from the string expression.

Example #5

Code:

DECLARE
Tst_String string(25) := 'Congratulations to our world';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'tu', 'ih'));
END;

Output:

Example #6

 Code:

DECLARE
Tst_String string(25) := 'Congratulations to our world';
BEGIN
dbms_output.put_line(REPLACE(Tst_String, 'tua', 'ih'));
END;

Output:

Explanation: In the above code, ‘Congratulations to our world’ is the string expression, ‘tua’ is the ‘string_to_replace’ and ‘ih’ is the ‘replacement_string’. So, in the above string expression, there is no string ‘tua’ present. So, if the searchable string is not present in the expression, the resulting string expression remains the same as the string 1. There would be no change in the input string expression.So, the resulting string in the above code would be ‘Congratulations to our world’.

Conclusion

The above description clearly explains what the REPLACE function is and how it works in PL/SQL. Though Oracle also provides the TRANSLATE function which works similar to that of REPLACE only one-to-one substitution is allowed in it whereas REPLACE allows the substitution of a sequence of the whole string. For a programmer, it is important to understand both of them and the difference between them to have a clear understanding.

Recommended Articles

This is a guide to PLSQL Replace. Here we also discuss the definition and how does the PLSQL Replace function works? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. Python Doubly Linked List
2. Deque in Python
3. Linear Search in Python
4. Shell sort in Python