BLOB（二进制大对象：text、ntext或image）类型数据的操作

最新推荐文章于 2024-04-23 15:46:25 发布

ciwan8376

最新推荐文章于 2024-04-23 15:46:25 发布

阅读量393

点赞数

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

三、总结

Text字段类型不能直接用replace函数来替换，必须用updatetext；
字段比较不能用　where 字段 = ‘某数据’,可以用like来代替；
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 数据范围内。
PATINDEX / CHARINDEX 函数都返回指定模式的开始位置。PATINDEX 可使用通配符，而 CHARINDEX 不可以。IS NULL、IS NOT NULL 和 LIKE，这些是 WHERE 子句中对 text / ntext类型有效的仅有的其它比较运算。除此之外，PATINDEX 也可用于 WHERE 子句中；
使用 TEXTVALID 来检查文本指针是否存在。在无有效文本指针时，不能使用 UPDATETEXT、WRITETEXT 或 READTEXT；例，SELECT 'Valid (if 1) Text data'
= TEXTVALID ('pub_info.logo', TEXTPTR(logo)) FROM pub_info WHERE logo like '%hello%'；
LEN只对短字符型有效，对于text/ntext/image类型，则使用DATALENGTH来得到数据长度；