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