text与image字段的转换

下面的例子实现text与image字段的转换：

use pubs
   
   

   
   
    
     
   
   
create table #t(id int,a image,b text)
   
   
go
   
   

   
   
    
     
   
   
declare @id int,@ps binary(16),@pd binary(16)
   
   
declare tb cursor local
   
   
for
   
   
select pub_id,textptr(pr_info) from pub_info
   
   
where textvalid('pub_info.pr_info',textptr(pr_info))=1
   
   
open tb
   
   
fetch tb into @id,@ps
   
   
while @@fetch_status=0
   
   
begin
   
   
         insert #t values(@id,0x,'')
   
   
         select @pd=textptr(a) from #t
   
   
         where textvalid('#t.a',textptr(a))=1
   
   
         if @@rowcount>0
   
   
                   updatetext #t.a @pd 0 null pub_info.pr_info @ps
   
   
         fetch tb into @id,@ps
   
   
end
   
   
close tb
   
   
deallocate tb
   
   
go
   
   

   
   
    
     
   
   
declare @ps binary(16),@pd binary(16)
   
   
declare tb cursor local
   
   
for
   
   
select textptr(a),textptr(b) from #t
   
   
where textvalid('#t.a',textptr(a))=1
   
   
and textvalid('#t.b',textptr(b))=1
   
   
open tb
   
   
fetch tb into @ps,@pd
   
   
while @@fetch_status=0
   
   
begin
   
   
         updatetext #t.b @pd 0 null #t.a @ps
   
   
         fetch tb into @ps,@pd
   
   
end
   
   
close tb
   
   
deallocate tb
   
   
go
   
   

   
   
    
     
   
   
select * from #t
   
   
drop table #t

------------------------------

以上代码出自邹建的《SQL Server 2000 开发与管理应用实例》,此处列出来学习一下，重点是几个sql server中专门用于处理text与ntext列的函数：

1）textptr:返回text与ntext列的文本指针值 格式：textptr(列名)

2）readtext:从 text、ntext 或 image 列读取 text、ntext 或 image 值
      格式：readtext 表名.列名 有效文本指针 开始读取之前跳过的字节数 要读取的字节数 [HOLDLOCK]（可以使文本值一直锁定到事务结束，其他用户只读）

运用上面两个函数，SQL帮助中有个例子：

USE pubs
GO
DECLARE @val varbinary(16)
SELECT @val = TEXTPTR(pr_info)
FROM pub_info
WHERE pub_id = '0736'
READTEXT pub_info.pr_info @val 4 10
GO

本例运行结果：
   
   

pr_info                                                                                                                                                                                                                                                         
  
  

-----------------------------------------------------------------------
  
  

 is sample
  
  

(1 row(s) affected)
  
  

3）textvalid:检查给定文本指针是否有效 格式：textvalid(表名.列名,文本指针)
  
  

4）updatetext:该函数用于在指定的位置更改text或者ntext列的一部分。
格式：

UPDATETEXT { table_name.dest_column_name dest_text_ptr }

    { NULL | insert_offset }

     { NULL | delete_length }

     [ WITH LOG ]

     [ inserted_data

    | { table_name.src_column_name src_text_ptr } ]

table_name . dest_column_name

要更新的表以及 text、ntext 或 image 列的名称。表名和列名必须符合标识符规则。可以选择是否指定数据库名和所有者名。

dest_text_ptr

指向要更新的 text、ntext 或 image 数据的文本指针的值（由 TEXTPTR 函数返回）。dest_text_ptr 必须为数据类型 binary(16)。

insert_offset

以零为基的更新起始位置。对于 text 或 image 列，insert_offset 是在插入新数据前要从现有列的起点跳过的字节数。对于 ntext 列，insert_offset 是字符数（每个 ntext 字符占用 2 个字节）。从此基数为零的起始点开始的现有 text、ntext 或 image 数据向右移，为新数据留出空间。值为 0 表示将新数据插入现有数据的开始处。值为 NULL 则将新数据追加到现有数据值后。

delete_length

从 insert_offset 位置开始的、要从现有 text、ntext 或 image 列中删除的数据长度。delete_length 值对于 text 和 image 列用字节指定，对于 ntext 列用字符指定。每个 ntext 字符占用 2 个字节。值为 0 表示不删除数据。值为 NULL 则删除现有 text 或 image 列中从 insert_offset 位置开始到末尾的所有数据。

WITH LOG

在 SQL Server 2000 中被忽略。在 SQL Server 2005 中，日志记录由数据库的实际恢复模型决定。

inserted_data

要插入现有 text、ntext 或 image 列的 insert_offset 位置的数据。这是单个 char、nchar、varchar、nvarchar、binary、varbinary、text、ntext 或 image 值。inserted_data 可以是文字或变量。

table_name.src_column_name

用作插入数据源的表或 text、ntext 或 image 列的名称。表名和列名必须符合标识符规则。

src_text_ptr

指向用作插入数据源的 text、ntext 或 image 列的文本指针值（由 TEXTPTR 函数返回）。

5）writeText: 允许对现有的 text、ntext 或 image 列执行最小日志记录的交互式更新。WRITETEXT 将覆盖受其影响的列中的所有现有数据。

格式：writeText 表名.列名 文本指针 [with log] 要存储的数据

例：

USE pubs;

GO

ALTER DATABASE pubs SET RECOVERY SIMPLE;

GO

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(pr_info)

FROM pub_info pr, publishers p

WHERE p.pub_id = pr.pub_id

   AND p.pub_name = 'New Moon Books'

WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'

GO

ALTER DATABASE pubs SET RECOVERY SIMPLE;

GO

其中“ALTER DATABASE pubs SET RECOVERY SIMPLE;”的含义见：http://community.csdn.net/Expert/topic/5539/5539653.xml?temp=5.247134E-02

虽然2005版的SQL帮助中提示：“后续版本的 Microsoft SQL Server 将删除”以上函数“功能。请避免在新的开发工作中使用该功能，并应着手修改当前还在使用该功能的应用程序。”但学习一下总是不多。

2005版的SQL帮助中提示用UPDATE 语句的 .WRITE 子句代替writetext和updatetext函数，格式如下：

Update 表名 set 列名.WRITE ( expression, @Offset , @Length ) where …

指定修改 column_name 值的一部分。expression 替换 @Length 单位（从 column_name 的 @Offset 开始）。只有 varchar(max)、nvarchar(max) 或 varbinary(max) 列才能使用此子句来指定。column_name 不能为 NULL，也不能由表名或表别名限定。

expression 是复制到 column_name 的值。expression 必须运算或隐式转换为 column_name 类型。如果将 expression 设置为 NULL，则忽略 @Length，并将 column_name 中的值按指定的 @Offset 截断。

@Offset 是 column_name 值中的起点，从该点开始编写 expression。@Offset 是基于零的序号位置，数据类型为 bigint，不能为负数。如果 @Offset 为 NULL，则更新操作将在现有 column_name 值的结尾追加 expression，并忽略 @Length。如果 @Offset 大于 column_name 值的长度，则 Microsoft SQL Server 2005 Database Engine 将返回错误。如果 @Offset 加上 @Length 超出了列中基础值的限度，则将删除到值的最后一个字符。如果 @Offset 加上 LEN(expression) 大于声明的基础大小，则将出现错误。

@Length 是指列中某个部分的长度，从 @Offset 开始，该长度由 expression 替换。@Length 的数据类型为 bigint，不能为负数。如果 @Length 为 NULL，则更新操作将删除从 @Offset 到 column_name 值的结尾的所有数据。

例：

UPDATE Production.Document

SET DocumentSummary .WRITE(N'Carefully inspect and maintain the tires and crank arms.',0,NULL)

WHERE DocumentID = 1;