REPLACE Function

Description

The REPLACE function searches for a character string, search_string, within a given character string, string, and replaces it with a character string, replacement_string. If the string to be replaced, replacement_string is omitted, all search_strings retrieved from string are removed. If NULL is specified as an argument, NULL is returned.

Syntax

REPLACE( string, search_string [, replacement_string ] )

 

string :

character string

NULL

 

search_string :

character string

NULL

 

replacement_string :

character string

NULL

Example

--it returns NULL when an argument is specified with NULL value

SELECT REPLACE('12345abcdeabcde','abcde',NULL);

replace('12345abcdeabcde', 'abcde', null)

======================

  NULL

 

--not only the first substring but all substrings into 'ABCDE' are replaced

SELECT REPLACE('12345abcdeabcde','abcde','ABCDE');

replace('12345abcdeabcde', 'abcde', 'ABCDE')

======================

  '12345ABCDEABCDE'

 

--it removes all of substrings when replace_string is omitted

SELECT REPLACE('12345abcdeabcde','abcde');

replace('12345abcdeabcde', 'abcde')

======================

  '12345'