SQL引數資料型別text對於replace函式的引數1無效

當執行語句：

update SupplyInfo 
set ProdInfo=replace(ProdInfo,'中國總代理','中國指定代理')
where CompanyID=83406

因為列ProdInfo為text型別，即SQL語句中用到replace時若操作列型別為text型別會有MSSQL報錯：引數資料型別text對於replace函式的引數1無效。
原因：對text或ntext型別的資料在查詢中不能進行字串操作。
解決方法：將text當作varchar（實際內容長度低於8000位元組時）或把ntext當作nvarchar（實際內容長度低於4000位元組時）。但是當text欄位內容長度超過8000或ntext欄位內容長度超過4000位元組時多出的位元組會被截斷而忽略掉。這時我們可以使用max型別來解決這個問題。

正確語句如下：

update SupplyInfo 
set ProdInfo=replace(cast(ProdInfo as varchar(max)),'中國總代理','中國指定代理')
where CompanyID=83406

附：max型別的介紹
varchar(max)、nvarchar(max) 和 varbinary(max) 統稱為大值資料型別。可以使用大值資料型別來儲存最大為 2^31-1 個位元組的資料。
max 說明符是在Microsoft SQL Server 2005 中引入的。此說明符增強了 varchar、nvarchar 和 varbinary 資料型別的儲存能力。

當 sp_tableoption 儲存過程的 ‘large value types out of row’ 選項設定為 OFF 時，大值型別的行記憶體儲限制為 8000 個位元組。當此選項設定為 ON 時，只在行記憶體儲 16 位元組的根。可查詢參考關於sp_tableoption (Transact-SQL)的內容。

大值資料型別在行為上和與之對應的較小的資料型別 varchar、nvarchar 和 varbinary 相似。這種相似使 SQL Server 能夠更高效地儲存和檢索大型字元、Unicode 和二進位制資料。

因為有了大值資料型別，使一些使用 SQL Server 的方式是使用早期版本的 SQL Server 中的 text、ntext 和 image 資料型別所沒有的。例如，在 SQL Server 2005 中，您可以定義能儲存大量資料（最多可達 2^31 位元組的字元、整數和 Unicode 資料）的變數。可查詢參考關於Transact-SQL 變數的內容。
Transact-SQL 變數示例：
DECLARE @MyURL nvarchar(2000);
set @MyURL=’http://blog.csdn.net/yiyelanxin‘;

下面是大值資料型別和 SQL Server 早期版本（如SQL 2000）中與之對應的資料型別之間的關係。

大值資料型別　　　　　早期版本中的 LOB
varchar(max)　　　　　text*
nvarchar(max)　　　　 ntext*
varbinary(max)　　　 image

SQL Server 6.5 版本的客戶端不支援 ntext 資料型別，因此無法識別 nvarchar(max)。

在SQL 2005中請使用 varchar(max)、nvarchar(max) 和 varbinary(max) 資料型別，而不要使用 text、ntext 和 image 資料型別。

大值資料型別在行為上和與之對應的較小的資料型別 varchar(n)、nvarchar(n) 和 varbinary(n) 相同。下面介紹大值資料型別在某些特定情形下的使用：

遊標
由於可以定義大值資料型別變數，便可以將 FETCH 返回的大值資料型別列中的資料賦給本地變數。可查詢參考關於 FETCH (Transact-SQL)的內容。
使用大值資料型別不影響遊標的強制實施遊標型別轉換用法。

成塊更新
UPDATE 語句現在支援 .WRITE( ) 子句對基礎大值資料列進行部分更新。這類似於 SQL Server 早期版本中所支援的對 text、ntext、image 資料型別的文字指標操作、WRITETEXT 和 UPDATETEX。可查詢參考關於UPDATE (Transact-SQL)的內容。

觸發器
支援對插入的和刪除的表中的大值資料型別列引用上使用 AFTER 觸發器。可查詢參考關於CREATE TRIGGER (Transact-SQL)的內容。

字串函式
內建的可操作字元和二進位制資料的字串函式有所增強，可支援大值資料型別的引數。這些函式包括：
COL_LENGTH
CHARINDEX
PATINDEX
LEN
DATALENGTH
SUBSTRING