SQL內置Function--文本和图像函数

 文本和图像函数

这些标量函数对文本或图像输入值或列执行操作，返回有关这些值的信息。

PATINDEX

TEXTPTR

TEXTVALID

这些文本和图像函数都是不确定性函数，每次调用它们时，即使是用相同的一组输入值，也可能返回不同的结果。有关函数确定性的更多信息，请参见确定性函数和非确定性函数。

1PATINDEX

返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。

语法

PATINDEX ( '%pattern%' , expression )

参数

pattern

一个字符串。可以使用通配符，但 pattern 之前和之后必须有 % 字符（搜索第一个和最后一个字符时除外）。pattern 是短字符数据类型类别的表达式。

expression

一个表达式，通常为要在其中搜索指定模式的列，expression 为字符串数据类型类别。

返回类型

int

注释

PATINDEX 对 text 数据类型很有用；除 IS NULL、IS NOT NULL 和 LIKE（这些是 WHERE 子句中对 text 类型有效的仅有的其它比较运算）外，PATINDEX 也可用于 WHERE 子句中。

如果 pattern 或 expression 为 NULL，则当数据库的兼容级别为 70 时 PATINDEX 返回 NULL；如果数据库兼容级别为 65 或更靠前，则仅当 pattern 和 expression 同时为 NULL 时，PATINDEX 返回 NULL。

示例

A. 在 PATINDEX 中使用模式

本示例查找模式"wonderful"在 titles 表中 notes 列的某一特定行中的开始位置。

USE pubs

GO

SELECT PATINDEX('%wonderful%', notes)

FROM titles

WHERE title_id = 'TC3218'

GO

下面是结果集：

----------- 

46         

(1 row(s) affected)

如果未通过 WHERE 子句限制要搜索的行，查询将返回表中的所有行，对在其中找到该模式的所有行报告非零值，对在其中未找到该模式的所有行报告零值。

B. 在 PATINDEX 中使用通配符

本示例使用通配符查找模式"won_erful"在 titles 表中 notes 列的某一特定行中的开始位置，其中下划线为代表任何字符的通配符。

USE pubs

GO

SELECT PATINDEX('%won_erful%', notes)

FROM titles

WHERE title_id = 'TC3218'

GO

下面是结果集：

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

46

(1 row(s) affected)

如果没有限制要搜索的行，查询将返回表中的所有行，对在其中找到该模式的所有行报告非零值。

2TEXTPTR

以 varbinary 格式返回对应于 text、ntext 或 image 列的文本指针值。检索到的文本指针值可用于 READTEXT、WRITETEXT 和 UPDATETEXT 语句。

语法

TEXTPTR ( column )

参数

column

待使用的 text、ntext 或 image 列。

返回类型

varbinary

注释

在 Microsoft SQL Server™ 2000 中，对于带行内文本的表，TEXTPTR 为要处理的文本返回一个句柄。即使文本值为空，用户仍能获得有效的文本指针。

如果表不带行内文本，并且 text、ntext 或 image 列还未由 UPDATETEXT 语句初始化，则 TEXTPTR 将返回空指针。

可使用 TEXTVALID 来检查文本指针是否存在。在无有效文本指针时，不能使用 UPDATETEXT、WRITETEXT 或 READTEXT。

下列函数和语句对 text、ntext 和 image 数据同样有用。

函数或语句	描述
PATINDEX('%pattern%', expression)	返回给定字符串在 text 或 ntext 列中的字符位置。
DATALENGTH(expression)	返回 text、ntext 和 image 列的数据长度。
SET TEXTSIZE	返回可用 SELECT 语句返回的 text、ntext 或 image 数据的极限大小（以字节为单位）。
SUBSTRING(text_column, start, length)	返回由给定 start 偏移量和 length 所指定的 varchar 字符串。此长度应小于 8 KB。

 

示例

A. 使用 TEXTPTR

下例使用 TEXTPTR 函数在 pubs 数据库的 pub_info 表中查找与 New Moon Books 相关联的 image 列 logo。文本指针放置在局部变量 @ptrval 中。

USE pubs

GO

DECLARE @ptrval varbinary(16)

SELECT @ptrval = TEXTPTR(logo) 

FROM pub_info pr, publishers p

WHERE p.pub_id = pr.pub_id 

   AND p.pub_name = 'New Moon Books'

GO

B. 使用带行内文本的 TEXTPTR

在 SQL Server 2000 中，行内文本指针必须在事务内部使用。示例如下：

CREATE TABLE t1 (c1 int, c2 text)

EXEC sp_tableoption 't1', 'text in row', 'on'

INSERT t1 VALUES ('1', 'This is text.')

GO

BEGIN TRAN

   DECLARE @ptrval VARBINARY(16)

   SELECT @ptrval = TEXTPTR(c2)

   FROM t1

   WHERE c1 = 1

   READTEXT t1.c2 @ptrval 0 1

COMMIT

C. 返回文本数据

下例从 pub_info 表中选择 pub_id 列和 pr_info 列的 16 字节文本指针。

USE pubs

GO

SELECT pub_id, TEXTPTR(pr_info)

FROM pub_info

ORDER BY pub_id

GO

下面是结果集：

pub_id                                    

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

0736   0x6c0000000000feffb801000001000100 

0877   0x6d0000000000feffb801000001000300 

1389   0x6e0000000000feffb801000001000500 

1622   0x700000000000feffb801000001000900 

1756   0x710000000000feffb801000001000b00 

9901   0x720000000000feffb801000001000d00 

9952   0x6f0000000000feffb801000001000700 

9999   0x730000000000feffb801000001000f00 

(8 row(s) affected)

下例显示如何在不使用 TEXTPTR 的情况下返回文本的前 8,000 个字节。

USE pubs

GO

SET TEXTSIZE 8000

SELECT pub_id, pr_info

FROM pub_info

ORDER BY pub_id

GO

下面是结果集：

pub_id pr_info                                                                                                                                                                                                                                                         

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

0736   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!                                                                                                           

0877   This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley is located in Washington, D.C.

This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley is located in Washi 

1389   This is sample text data for Algodata Infosystems, publisher 1389 in the pubs database. Algodata Infosystems is located in Berkeley, California.

9999   This is sample text data for Lucerne Publishing, publisher 9999 in the pubs database. Lucerne publishing is located in Paris, France.

This is sample text data for Lucerne Publishing, publisher 9999 in the pubs database. Lucerne publishing is located in 

(8 row(s) affected)

D. 返回特定文本数据

下例在 pubs 数据库的 pub_info 表中查找与 pub_id 0736 相关联的 text 列 (pr_info)。下例首先声明一个局部变量 @val。然后将文本指针（长二进制字符串）置于 @val 中，并将其作为参数提供给 READTEXT 语句，该语句将返回从第五个字节（偏移量为 4）开始的 10 个字节。

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

下面是结果集：

(1 row(s) affected)

pr_info                                                                                                                                                                                                                                                        

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

 is sample

3TEXTVALID

一个 text、ntext 或 image 函数，用于检查给定文本指针是否有效。

语法

TEXTVALID ( 'table.column' , text_ ptr )

参数

table

要使用的表名。

column

要使用的列名。

text_ptr

要检查的文本指针。

返回类型

int

注释

如果指针有效则返回 1，无效则返回 0。请注意，text 列的标识符必须包含表名。在无有效文本指针时，不能使用 UPDATETEXT、WRITETEXT 或 READTEXT。

下列函数和语句对 text、ntext 和 image 数据同样有用。

函数或语句	描述
PATINDEX('%pattern%', expression)	返回给定字符串在 text 或 ntext 列中所处的字符位置。
DATALENGTH(expression)	返回 text、ntext 和 image 列的数据长度。
SET TEXTSIZE	返回可用 SELECT 语句返回的 text、ntext 或 image 数据的极限大小（以字节为单位）。

 

示例

下例报告 pub_info 表的 logo 列中各个值是否存在有效文本指针。

USE pubs

GO

SELECT pub_id, 'Valid (if 1) Text data' 

   = TEXTVALID ('pub_info.logo', TEXTPTR(logo)) 

FROM pub_info

ORDER BY pub_id

GO

下面是结果集：

pub_id Valid (if 1) Text data 

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

0736   1                      

0877   1                      

1389   1                      

1622   1                      

1756   1                      

9901   1                      

9952   1                      

9999   1                      

(8 row(s) affected)