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 |
---|---|
|
Source string containing the substring to replace. |
|
String of characters to be replaced in the original string. If |
|
String of characters that are used to replace all occurrences of the search string in the original string. If |
Description
-
REPLACE
returns a string where every occurrence of theSearchString
is replaced withReplacementString
in the sourceString
. -
String
,SearchString
andReplacementString
can be any of the following data types:CHAR
,VARCHAR2
,NCHAR
,NVARCHAR2
,CLOB
, orNCLOB
. Both TimesTen and Oracle Database data types are supported. All non-character data types, except forBLOB
, are implicitly converted to a string data type. -
If
String
is aCHAR
orVARCHAR2
, the returned string is of data typeVARCHAR2
. IfString
is anNCHAR
orNVARCHAR2
, the returned string is of data typeNVARCHAR2
. ForCLOB
orNCLOB
data types, the data type returned is the same as the data type provided inString
. The character set is the same as the sourceString
. -
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.