BLOB（二進位制大物件：text、ntext或image）型別資料的操作

SQL SERVER 2000中，

• 字元型別一般用char和varchar資料型別(短字元型)，最長為8000位元組，超過8000個位元組的文字就要使用ntext或者text資料型別來儲存；
• 二進位制型別一般用binary、varbinary ，最長為8 KB，儲超過 8 KB 的可變長度的二進位制資料，如 Microsoft Word 文件、Microsoft Excel 電子表格、包含點陣圖的影像、圖形交換格式 (GIF) 檔案和聯合影像專家組 (JPEG) 檔案，使用image 資料型別來儲存。
• ntext、text 和 image 資料型別在單個值中可以包含非常大的資料量，最大可達 2 GB。

一、下面的函式和語句可以與 ntext、text 或 image 資料一起使用。

函式	語句
DATALENGTH	READTEXT
PATINDEX	SET TEXTSIZE
SUBSTRING	UPDATETEXT
TEXTPTR	WRITETEXT
TEXTVALID

二、用法示例

1、替換

--建立資料測試環境
create table #tb(aa text)
insert into #tb select 'abc123abc123,asd'

--定義替換的字串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='123', --要替換的字串
         @d_str='000' --替換成的字串

--字串替換處理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(aa),@rplen=len(@s_str),@postion=charindex(@s_str,aa)-1 from #tb
while @postion>0
begin
   updatetext #tb.aa @p @postion @rplen @d_str
   select @postion=charindex(@s_str,aa)-1 from #tb
end

--顯示結果
select * from #tb

--刪除資料測試環境
drop table #tb

2、全部替換

DECLARE @ptrval binary(16)
DECLARE @ptrvld int
SELECT @ptrval = TEXTPTR(aa), @ptrvld = TEXTVALID('#tb.aa', TEXTPTR(AA))  FROM  #tb  WHERE aa like '%資料2%'
-- 一定要加上條件判斷，否則若找不到目標檔案指標下一句SQL就會報錯（很重要！）
if @ptrval is not null and  @ptrvld = 1
   UPDATETEXT #tb.aa @ptrval 0 null '資料3'

3、在欄位尾新增

--定義新增的的字串
declare @s_str varchar(8000)
select @s_str='*C'   --要新增的字串
--字串新增處理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(detail) from test where id='001'
updatetext test.detail @p null null @s_str

三、總結

1. Text欄位型別不能直接用replace函式來替換，必須用updatetext；
2. 欄位比較不能用　where 欄位 = ‘某資料’,可以用like來代替；
3. updatetext時，若dest_text_ptr值為NULL時會報錯，需注意。錯誤資訊：向UpdateText 函式傳遞了 NULL textptr（text、ntext 或 image 指標）；注意，BLOB列為NULL而所在行不為空時，dest_text_prt為NOT NULL，若BOLB所在行為空，則dest_text_prt為NULL。delete_length必須小於等於欄位總長度，否則報錯：刪除長度  不在可用的 text、ntext 或 image 資料範圍內。
4. PATINDEX / CHARINDEX 函式都返回指定模式的開始位置。PATINDEX 可使用萬用字元，而 CHARINDEX 不可以。IS NULL、IS NOT NULL 和 LIKE，這些是 WHERE 子句中對 text / ntext型別有效的僅有的其它比較運算。除此之外，PATINDEX 也可用於 WHERE 子句中；
5. 使用 TEXTVALID 來檢查文字指標是否存在。在無有效文字指標時，不能使用 UPDATETEXT、WRITETEXT 或 READTEXT；例，SELECT 'Valid (if 1) Text data'
      = TEXTVALID ('pub_info.logo', TEXTPTR(logo)) FROM pub_info WHERE logo like '%hello%'； 
6. LEN只對短字元型有效，對於text/ntext/image型別，則使用DATALENGTH來得到資料長度；

[@more@]