REPLACE

Given a series of three strings (representing the search string, string to be replaced, and replacement string, respectively), the REPLACE function substitutes the replacement string for all instances of the string to be replaced that are contained in the search string.

Syntax

REPLACE (search_string, string_to_be_replaced, replacement_string)

Remarks

•

The string_to_be_replaced and the replacement_string must be of the same type (string or binary).

•

All occurrences of the string_to_be_replaced within the search_string are replaced with the replacement_string.

•

The string_to_be_replaced and the replacement_string must be enclosed within single-quotes.

•

If any of the input strings is NULL, the output is also NULL. Otherwise, the output is a string.

The following table lists the input types that you can use in REPLACE, and their corresponding output types.

Data Type of search_string	Data Type of string_to_be_replaced	Data Type of replacement_string	Output Data Type
CHAR, VARCHAR, LONGVARCHAR, STRING	Same as search_string.	Same as string_to_be_ replaced.	Same as string_to_be_ replaced.
CHAR, LONGVARCHAR, NULL, STRING, VARCHAR	NULL	Same as search_string.	NULL
NULL	CHAR, VARCHAR, LONGVARCHAR, STRING	Same as string_to_be_ replaced.	NULL
CHAR, LONGVARCHAR, STRING, VARCHAR	Same as search_string.	NULL	NULL

Example

SELECT REPLACE (products.ProductName, 'USB 2.0', 'USB 3.0') Replaced

FROM /shared/examples/ds_inventory/products products