SQL Server中的文本函数和图像函数介绍

1、查找特定字符串PATINDEX

语法与字符串的patindex一样。

2、获取文本指针TEXTPTR

SQLServer在存储文本类型(ntext、text)和图像数据类型(image)时，默认情况是与常见的基本数据类型(如char、int等)这样的数据时单独存储的，也就是说，如果一个表中的字段有基本类型和图像文本类型，那么在输入数据时，基本类型和图像文件数据并不是保存在同一个页面上，而是分离存储的。分离存储后，那么基本类型数据和文本图像类型数据所在的页面就是通过文本指针建立联系，是一个16字节的二进制类型指针。在基本类型数据页面中存储的就是这些指向数据的指针，这样在需要的时候通过指针来操作文本和图像数据。Textptr函数就可以用于获得文本和图像数据存储的文本指针，这个指针获得后就可以被操作文本和图像数据的函数(writetext、readtext和updatetext等)进行操作。如果没有有效的指针，文本和图像函数是无法操作数据的。

语法结构：

textptr( column )

这里的column就是表中的文本和图像数据列(text、ntext和image)名称。

返回值：

varbinary类型16字节指针。

配合使用的函数：

在编写含有TEXTPTR函数的T-SQL代码时，下表所示的函数经常被使用。

• patindex('%pattern%',expression)
  返回字符串在text或ntext列中的字符位置
• datalength(expression)
  返回text、ntext和image列中数据的长度
• set textsize
• 返回对select语句所返回的text、ntext或image数据的限制(字节数)。settextsize 0表示不限制。
• substring(text_column,start,length)
  返回二进制列中又指定的start偏移量和length指定的varchar类型的字符串。字符串的长度硬小于8kb

3、检测文本指针是否有效TEXTVALID

textvalid函数用于检测文本指针是否有效。

语法结构：

textvalid( 'table.column',text_ptr )

参数说明：

• table：表的名称，必须包含
• column：表中的二进制数据列(text、ntext和image)名称
• text_ptr：要检测的文本指针

返回值：

int，如果指针有效返回1，否则分那会0。

4、读取文本图像列内容READTEXT

readtext函数用于读取文本或图像列的内容，按照指定的偏移量开始读取指定字节数。如果是图像列(image)，这样读取的是字节数，而不是现实图像本身。

语法结构：

readtext{ table.column text_ptr offset size } [holdlock]

参数说明：

• table：表名称
• column：列名称
• text_ptr：有效的文本指针，即16字节的binary数据。
• offset：开始读取text、image类型数据之前，要跳过的字节数。读取ntext类型数据之前要跳过的字符数。
• size：读取的字节数(使用text或image数据类型时)或字符数(使用ntext数据类型时)。如果size为0，则读取4KB数据。
• holdlock：使文本值被锁定以进行读取，直到十五结束为止。其他用户可以读取该值，但不能对其进行修改。

返回值：

varchar字符。

5、写入文本图像列内容WRITETEXT

writetext函数用于向文本或图像写入内容，同时可以选择为最小日志记录，这样写入操作产生的日志写的I/O次数少，对数据库性能影响小。

语法结构：

writetext{ table.column text_ptr } [ with log ] { data }

参数说明：

• table：表名称
• column：列名称
• text_ptr：有效的文本指针，即16字节的binary数据。
• with log：由数据库的恢复模式来决定writetext操作产生的日志行为。因为text、ntext、imge类型的数据一般情况下都比较大。比如一幅图片可能上百KB，我们知道一个数据页面的容量是标准的8KB。所以一幅图片很可能就需要几时个甚至更多的数据页面，所以writetext操作所产生的I/O次数是很多的，而过多的I/O次数将影响数据库的性能。该参数的引入是交给数据库的恢复模式来决定的writetext操作将产生的日志行为。当数据库公仔在完全恢复模式时，产生的日志最多，因此对数据库性能的影响也最大。当工作在简单恢复模式时，产生的日志最少。
• data：要存储的text、ntext或image类型的数据。writetext以交互方式插入的最大文本长度约为120KB。

6、更新文本图像列内容UPDATETEXT

updatetext函数更新文本或图像列中的内容。与writetext的区别在于该函数可以仅更新列中的一部分，而writetext是更新整个列。

语法结构：

updatetext{ table.name.dest_column_name dest_text_ptr }
　　　　　　　　　　　 { null | insert_offset }
　　　　　　　　　　　 { with log }
　　　　　　　　　　　 { inserted_data | { table.name.src_column_name src_text_ptr } }

参数说明：

• table_name：表名称。
• dest_column_name：列名称。
• dest_text_ptr：有效的文本指针，即16字节的binary数据。
• insert_offset：以0为基础的更新其实位置，对于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位置开始到末尾的所有数据。
• withlog：日子记录由数据库的当前模式决定。
• inserted_date：要插入现有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函数获得。