REPLACE

REPLACE substitutes a sequence of characters in a given string with another set of characters or removes the string entirely.

SQL syntax

REPLACE (String, SearchString [,ReplacementString] )

Parameters

REPLACE has the parameters:

Parameter Description

String

Source string containing the substring to replace.

SearchString

String of characters to be replaced in the original string. If SearchString is NULL, the original String is returned without any modification.

ReplacementString

String of characters that are used to replace all occurrences of the search string in the original string. If ReplacementString is omitted or NULL, all occurrences of SearchString are removed from the source String.

Description

  • REPLACE returns a string where every occurrence of the SearchString is replaced with ReplacementString in the source String.

  • String, SearchString and ReplacementString can be any of the following data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Both TimesTen and Oracle Database data types are supported. All non-character data types, except for BLOB, are implicitly converted to a string data type.

  • If String is a CHAR or VARCHAR2, the returned string is of data type VARCHAR2. If String is an NCHAR or NVARCHAR2, the returned string is of data type NVARCHAR2. For CLOB or NCLOB data types, the data type returned is the same as the data type provided in String. The character set is the same as the source String.

  • If the returned string length is zero, NULL is returned for Oracle Database data types and a zero length string is returned for TimesTen data types. See Data Types for details on all data types.

Examples

The following prints out all locations in Canada, replacing the country code of CA with Canada for easier readability.

Command> SELECT location_id, street_address,
           city, state_province, postal_code,
         REPLACE(country_id, 'CA', 'Canada') 
         FROM LOCATIONS 
         WHERE country_id LIKE 'CA';

< 1800, 147 Spadina Ave, Toronto, Ontario, M5V 2L7, Canada >
< 1900, 6092 Boxwood St, Whitehorse, Yukon, YSW 9T2, Canada >
2 rows found.