REPLACE (Transact-SQL)REPLACE (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

將指定字串值的所有相符項目取代成另一個字串值。Replaces all occurrences of a specified string value with another string value.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

REPLACE ( string_expression , string_pattern , string_replacement )  

引數Arguments

string_expressionstring_expression
這是要搜尋的字串運算式Is the string expression to be searched. string_expression 可以是字元或二進位資料類型。string_expression can be of a character or binary data type.

string_patternstring_pattern
這是要尋找的子字串。Is the substring to be found. string_pattern 可以是字元或二進位資料類型。string_pattern can be of a character or binary data type. string_pattern不可以是空字串 (''),並且不得超過分頁所能容納的最大位元組數目。string_pattern cannot be an empty string (''), and must not exceed the maximum number of bytes that fits on a page.

string_replacementstring_replacement
這是取代字串。Is the replacement string. string_replacement 可以是字元或二進位資料類型。string_replacement can be of a character or binary data type.

傳回類型Return Types

如果其中一個輸入引數是 nvarchar 資料類型,便傳回 nvarchar;否則,REPLACE 會傳回 varcharReturns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.

如果任何一個引數是 NULL,便會傳回 NULL。Returns NULL if any one of the arguments is NULL.

如果 string_expression 的類型不是 varchar(max)nvarchar(max),則 REPLACE 會將傳回值截斷為 8,000 位元組。If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. 若要傳回大於 8,000 位元組的值,string_expression 必須明確轉換成大數值資料類型。To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

RemarksRemarks

REPLACE 會以輸入的定序為基礎來執行比較。REPLACE performs comparisons based on the collation of the input. 若要執行指定定序的比較,您可以利用 COLLATE,將明確定序套用至輸入。To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

0x0000 (char(0)) 是 Windows 定序中未定義的字元,而且不得包含在 REPLACE 中。0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in REPLACE.

範例Examples

下列範例利用 cde 來取代 abcdefghi 中的 xxx 字串。The following example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE('abcdefghicde','cde','xxx');  
GO  

以下為結果集:Here is the result set.

------------  
abxxxfghixxx  
(1 row(s) affected)  

下列範例使用 COLLATE 函數。The following example uses the COLLATE function.

SELECT REPLACE('This is a Test'  COLLATE Latin1_General_BIN,  
'Test', 'desk' );  
GO  

以下為結果集:Here is the result set.

------------  
This is a desk  
(1 row(s) affected)  

另請參閱See Also

CONCAT (Transact-SQL)CONCAT (Transact-SQL)
CONCAT_WS (Transact-SQL)CONCAT_WS (Transact-SQL)
FORMATMESSAGE (Transact-SQL)FORMATMESSAGE (Transact-SQL)
QUOTENAME (Transact-SQL)QUOTENAME (Transact-SQL)
REVERSE (Transact-SQL)REVERSE (Transact-SQL)
STRING_AGG (Transact-SQL)STRING_AGG (Transact-SQL)
STRING_ESCAPE (Transact-SQL)STRING_ESCAPE (Transact-SQL)
STUFF (Transact-SQL)STUFF (Transact-SQL)
TRANSLATE (Transact-SQL)TRANSLATE (Transact-SQL)
資料類型 (Transact-SQL) Data Types (Transact-SQL)
字串函數 (Transact-SQL)String Functions (Transact-SQL)