使用Text/Image字段

關於 Text、Ntext 和 Image 的簡短教學課程

作者：Joseph Gama

Text、 Ntext 和 Image 資料的歷史久遠，但他們的微妙之處卻常遭到忽略。在此教學課程當中，Joseph Gama 提供了一個實作和使用這些特殊資料型別的快速概觀。他特別將本文獻給丹佛大都會州立學院 (Metropolitan State College of Denver) 的 Dr. Aaron Gordon、Dr. Earl Hasz、Dr. Jerry Shultz 和 Dr. Shahar Boneh，感謝他們傑出的教學；也獻給 Adam 和 Karen Schwarz，感謝他們全力的支援。本文包含至英文網頁連結。

資 料庫不論是在規模和複雜度方面都一直不斷的增長，部份原因是因為現今的硬體和軟體讓我們可以存放驚人的資料量 — 包括多媒體和文件資料。JPG、PNG、MP3、DOC/RTF、HTML、Unicode 和 XML 資料都可以在 SQL Server 資料庫中存成 Image、Text 或 Ntext。

一般而言，Text 是用來儲存大型的 ASCII 字元字串，Ntext 用於儲存 Unicode 字元字串，而 Image 是用於儲存二進位影像資料。擔心大小嗎？Text 提供您多達 2^31 - 1 (2,147,483,647) 可變長度的非 Unicode 字元，Ntext 提供多達 2^30 - 1 (1,073,741,823) 字元，而 Image 提供多達 2^31 - 1 (2,147,483,647) 位元組。Ntext 實際的儲存大小 (以位元組計) 是所輸入之字元數的兩倍。Ntext 的 SQL-92 同義字是國家文字 (National Text)。

那他們是怎麼個運作法？他們是使用指標來參考資料。特殊的函數可讓指標新增 (Add)、展開 (Extract) 或從中移除 (Remove) 資料。[表 1] 提供其優點和限制的摘要。

[表 1] Text、Ntext 和 Image 資料型別的優點和限制。

可行	不可行
作為預存程序的輸入或輸出參數。	與 DECLARE、SET 或 FETCH 一起使用，表示您無法將之用作變數 (像是使用其他資料型別)。
作為 UDF 的輸入項。	作為 UDF 的回傳資料型別 (時間戳計 (Timestamp) 亦不行)。
可將多達 8,000 位元組轉換成其他資料型別。	從含有 FETCH 的資料指標中擷取，除非進行轉換。
加入 UNION ALL 子句。	與 sql_variant 並用。
	在 GROUP BY 子句中比較、排序和使用。唯一的例外是使用 IS NULL 或 LIKE 時。(一個簡單的因應措施是使用 CONVERT，或使用由使用者定義、可傳回其他資料型別的函數)。
	加入 UNION 子句，因為此類的 UNION 相當於 DISTINCT 子句，而且它可能因 Text、Ntext 和 Image 資料型別無法進行排序而導致錯誤。

Text 和 Image 函數

此處將說明一些 Text 和 Image 函數，展示每一個的語法和加上輸出結果的範例。

TEXTPTR
TEXTPTR 會傳回一個 16 位元組長的 Varbinary，這是參考 Text、Ntext 或 Image 資料行的文字指標值。

語法： TEXTPTR ( column )

--TEXTPTR sample, create a text-pointer, see its value
create table #t (n ntext)
insert #t values('abcdef')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(n) FROM #t 
print @ptrval 
drop table #t

輸出：

0xFFFF6900000000004D00000001000000

TEXTVALID
TEXTVALID 會傳回一個 Int，這在文字指標有效時值為 1，否則為 0。

語法： TEXTVALID ( 'table.column' , text_ptr )

--TEXTVALID sample, creates a text-pointer, tests it
create table #t (n ntext)
insert #t values('abxyef')
DECLARE @ptrval binary(16), @ptrval2 binary(16)
SELECT @ptrval = TEXTPTR(n) FROM #t
if TEXTVALID('#t.n',@ptrval)=1
  print '@ptrval has a valid text pointer.'
else	
  print '@ptrval has an invalid text pointer.'
if TEXTVALID('#t.n',@ptrval2)=1	
  print '@ptrval2 has a valid text pointer.'
else  print '@ptrval2 has an invalid text pointer.'
drop table #t

輸出：

@ptrval has a valid text pointer.
@ptrval2 has an invalid text pointer.

SET TEXTSIZE
SET TEXTSIZE 屬於 Int 值，它會設定在使用 SELECT 陳述式時，所要傳回之 Text 和 Ntext 資料的大小。

語法： SET TEXTSIZE { number }

--SET TEXTSIZE sample
create table #t (n ntext)
insert #t values('abcdefghijk')
SET TEXTSIZE 10--ntext is unicode, 2 bytes/character
select * from #t
SET TEXTSIZE 20--ntext is unicode, 2 bytes/character
select * from #t
drop table #t

輸出：

abcde
abcdefghij

@@TEXTSIZE
@@TEXTSIZE 屬於 Int 值，它會傳回在使用 SELECT 陳述式時，所要傳回之 Text 和 Ntext 資料的大小。此值是經由 SET TEXTSIZE 設定。

語法： @@TEXTSIZE

--@@TEXTSIZE sample
SET TEXTSIZE 10--ntext is unicode, 2 bytes/character
print @@TEXTSIZE
SET TEXTSIZE 20--ntext is unicode, 2 bytes/character
print @@TEXTSIZE

輸出：

10
20

WRITETEXT
WRITETEXT 會覆寫 Text、Ntext 或 Image 資料行中的資料。

語法： WRITETEXT { table.column text_ptr } [ WITH LOG ] { data }

--WRITETEXT sample
create table #t (n ntext)
insert #t values('abc')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(n) 
FROM #t
WRITETEXT #t.n @ptrval 'def'
select * from #t
drop table #t

輸出：

def

UPDATETEXT
UPDATETEXT 會變更現有 Text、Ntext 或 Image 資料行的資料。

語法： 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 } ]

--UPDATETEXT sample insertion only
create table #t (n ntext)
insert #t values('bd')
DECLARE @ptrval binary(16), @i int
SELECT @ptrval = TEXTPTR(n) 
FROM #t
UPDATETEXT #t.n @ptrval 0 0 'a'--insert at beginning
select * from #t
UPDATETEXT #t.n @ptrval 2 0 'c'--insert in the middle
select * from #t
set @i=(select DATALENGTH(n) from #t)/2
--/2 only if ntext, 2 bytes/character
print @i
UPDATETEXT #t.n @ptrval @i 0 'e'--insert at the end
select * from #t
drop table #t

輸出：

abd
abcd
abcde

刪除和插入範例：

--UPDATETEXT sample deletion+insertion
create table #t (n ntext)
insert #t values('abxyef')
DECLARE @ptrval binary(16), @i int
SELECT @ptrval = TEXTPTR(n) 
FROM #t
UPDATETEXT #t.n @ptrval 2 2 'cd'--insert 2, delete 2 
--chars starting at position 2
select * from #t
drop table #t

輸出：

abcdef

READTEXT
READTEXT 會從 Text、Ntext 或 Image 資料行讀取特定的資料量。

語法： READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]

--READTEXT sample
create table #t (n ntext)
insert #t values('abcdefghijk')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(n) FROM #t
READTEXT #t.n @ptrval 3 8
--read 8 characters starting at position 3
drop table #t

輸出：

defghijk

DATALENGTH
DATALENGTH 會傳回 Text、Ntext 或 Image 資料行的大小 (位元組數)。

語法： DATALENGTH ( expression )

--DATALENGTH sample
create table #t (n ntext)
insert #t values('1234567890')
DECLARE @i int
set @i=(select DATALENGTH(n) from #t)
--it should return the length in bytes=2*UNICODE length
PRINT @i
drop table #t

輸出：

20

PATINDEX
PATINDEX 屬於 Int 值，它會傳回 Text、Ntext 或 Image 資料行中第一個相同模式所出現的位置，如果找不到相同的模式，則為 0。

語法： PATINDEX ( '%pattern%' , expression )

--PATINDEX sample
create table #t (n ntext)
insert #t values('Hello Tim, long time no see!')
SELECT PATINDEX('%tim%', n) FROM #t
SELECT PATINDEX('%time%', n) FROM #t
drop table #t

輸出：

7
17

CONVERT
CONVERT 會傳回從一種資料型別轉換成另一種的運算式。

語法： CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

--CONVERT sample
create table #t (n ntext)
insert #t values('Hello Tim, long time no see!')
DECLARE @c nvarchar(5)
SET @c=(select convert(nvarchar(5),n) from #t)
print @c
drop table #t

輸出：

Hello

CAST
CAST 會傳回從一種資料型別轉換 (Cast) 成另一種的運算式。

語法： CAST ( expression AS data_type )

--CAST sample
create table #t (n ntext)
insert #t values('Hello Tim, long time no see!')
DECLARE @c nvarchar(5)
SET @c=(select CAST ( n  AS nvarchar(5) ) from #t)
print @c
drop table #t

輸出：

Hello

一般實作

有了以上的一些基本瞭解之後，讓我們來看看一些常見的用途。

將 Text、Ntext 或 Image 資料行存入檔案
我建立了三個個別的預存程序，向您說明如何將 Text、Ntext 或 Image 的資料行型別存入檔案。您可在隨附的下載檔案中找到這些和所有其他範例的程式碼。

--saveText2file sample
create table ##t (n text)
insert ##t values('Hello Tim, long time no see!')
EXEC saveText2file 'c:\test.txt', '##t','n', ''
drop table ##t

--saveNtext2file sample
create table ##t (n ntext)
insert ##t values('Hello Tim, long time no see!')
EXEC saveNtext2file 'c:\test.txt', '##t','n', ''
drop table ##t

--saveImage2file sample
exec saveImage2file 'c:\Category1.bak', 
'Northwind..Categories', 'Picture', 
'where categoryid=1'

從檔案更新 Text、Ntext 或 Image 資料行
由 於 TEXTPTR、WRITETEXT 和 UPDATETEXT 並不允許使用變數名稱來定義資料表或資料行參數，所以若要將檔案的內容讀入資料行中，就需要使用動態 SQL。預存程序 readImageFromfile 可同時處理 Image 和 Varchar 資料型別，因為它可將資料讀成二進位，並且不需要使用暫時資料表即可寫入。Ntext 可使用 readNtextFromfile 加以讀取。

--readImageFromfile sample 
--reading a text column from a file
create table ##t (n text)
insert ##t values('Hi Tim, long time no see!')
EXEC readImageFromfile 'c:\hello.txt', '##t','n', ''
select * from ##t
drop table ##t

輸出：

Hello

相同的程式碼也可用於 Image 資料行。唯一要記住的事是 Ntext 資料行不應該接收 ASCII 文字檔的資料 — 而 Text 資料行也不應該接收 Unicode 資料 — 因為此預存程序並不會執行任何轉換。資料是以原始格式呈現。

預 存程序 readImageFromfile2 會附加資料而不是取代原始的內容，而 readNtextFromfile 則會讀取 Ntext (Unicode) 資料。附加資料比更新資料還簡單，因為更新作業首先是將原始的資料以第一個新資料區塊取代，接著再持續附加新資料區塊，而附加作業則不需要執行第一個步 驟。讀取 Ntext 基本上跟讀取任何其他資料型別一樣，除了 Unicode 必須是 Little Endian 以外 (許多文字處理器會在 Unicode 文字檔的開頭加上 0xFFFE)。預存程序 readNtextFromfile 擁有一些額外的程式碼可以移除 0xFFFEm，即「位元順序標記」(Byte Order Mark，BOM)，這是用來辨別 Unicode 的 Big Endian 和 Little Endian 變數。

請參閱下載檔案中、另一個稱為 readNtextFromfileBigEndian 的預存程序，它可從檔案讀取 Unicode Big Endian，並將之轉換成 Little Endian。

將物件的文字存入檔案
透 過在 Ntext 資料行使用暫存資料表，即可讀取規則、預設值、未加密預存程序、UDF、觸發器，或檢視的文字，並將其存入檔案。透過 INSERT EXEC 技術可建立暫存資料表，並接著插入新記錄。這筆記錄將包含物件從 sysobjects 和系統預存程序 sp_helptext 擷取的文字。這是利用下列程式碼在預存程序 saveobj 中完成的：

create table #temp(s nvarchar(4000))
insert #temp
exec sp_helptext @object

預存程序 saveobj 可將任何規則、預設值、未加密預存程序、UDF、觸發器或檢視儲存至文字檔，使之非常實用於備份和文件記載用途。

--saveobj sample, saving object hello
exec saveobj 'c:\hello.sql', 'hello'

從檔案讀取物件的文字
讀取超過 8,000 位元組的檔案需要使用動態 SQL，因為許多緩衝區會據檔案的大小填滿，並在稍後執行。預存程序 readobj 可以與迴圈並用，即可使用迴圈來決定需要建立幾個暫存變數，以建立動態 SQL。每一個暫存變數可以保留 8,000 位元組的資料，這並不會限制匯入檔案的大小。使用暫存變數名稱加上一個字元和漸增的數字，即可用來區別暫存變數。

--readobj sample, reading object hello
exec readobj 'c:\hello.sql'

超過 8,000 位元組的 INSERT 或 UPDATE 陳述式
預 存程序 readobj 可從文字檔建立規則、預設值、未加密預存程序、UDF、觸發器或檢視物件。它也容許 INSERT、UPDATE、DELETE，或其他超過 8,000 位元組的陳述式。如果陳述式並不在文字檔中，而是在巨大的 SQL 字串中，使用簡單的預存程序即可加以執行。

CREATE PROCEDURE exec_ntext (@SQL ntext)
--Execute a gigantic SQL statement
AS
EXEC (@SQL)

利用使用者定義的函數來比較 Text、Ntext 或 Image 資料行
若要比較兩個資料行，最快、最簡單的方式是依大小來進行比較，但這套方法具有明顯的限制，因為兩個資料行很有可能有相同的長度但內容卻截然不同：

CREATE FUNCTION  testlength (@a ntext, @b ntext)
--returns true if both inputs have the same length
RETURNS bit AS  
BEGIN 
declare @temp_bit bit
if datalength(@a)=datalength(@b)
  set @temp_bit= 1
else
  set @temp_bit=0
return @temp_bit
END

(在前述範例中的輸入項是 Ntext，但也可以是 Text 或 Image)。

比較兩個資料行的最佳方法，是比較整個內容或比較其相關的子集：

CREATE FUNCTION  testequality (@a ntext, @b ntext)
--returns 1 if the comparison result is true
RETURNS bit AS  
BEGIN 
declare @temp_bit bit
if  @a like @b
  set @temp_bit= 1
else
  set @temp_bit=0
return @temp_bit
END

模式比對 (第二個輸入變數)，如果使用萬用字元加以定義，可讓這套方法特別有效。您可能已經知道 SQL Server 的萬用字元有：

• % 表示任何大小的字串，甚至是空白字串。
• _ 表示只有一個字元的字串。
• [ ] 表示包含在一組字元 ([fhsvf]) 或一字元範圍 ([k-t]) 中的一個字元。
• [^] 表示不包含在一組字元 ([^fhsvf]) 或一字元範圍 ([^k-t]) 中的一個字元。

判定影像的類型
確定影像是點陣圖、JPEG、PNG 還是 TIFF 的一個快捷巧妙方法，就是檢查該影像的第一個位元組來確定他們是否符合該些格式的標頭規格。這套方法並非萬無一失，因為它可能無法正確判定原始資料，不過它是使用更煞費苦心的識別函數之前應該嘗試的第一步：

CREATE FUNCTION  getImageType (@a image)
--returns the type of image format
RETURNS varchar(4) AS  
BEGIN 
declare @out varchar(4), @temp varbinary(8)
SET @temp=convert(varbinary(8), @a)
SET @out=CASE WHEN LEFT(@temp,2)=0x424D THEN 'BMP' 
WHEN LEFT(@temp,2)=0xFFD8 THEN 'JPG' 
WHEN LEFT(@temp,8)=0x89504E470D0A1A0A THEN 'PNG' 
WHEN (LEFT(@temp,2)=0x4949)OR(LEFT(@temp,2)=0x4D4D)
THEN 'TIFF' 
ELSE '' END
return @out
END

這些和其他範例可在下載檔案中取得。祝您在自己的應用程式中探索和實現這些技巧時，能夠一帆風順！

下載 NTEXTCODE.exe

若要瞭解更多有關 SQL Server Professional 和 Pinnacle Publishing 的資訊，請造訪他們的網站，網址為 http://www.pinpub.com/

注意： 這不是 Microsoft Corporation 的網站。Microsoft 無法控管其網站內容。

本 文源自 2003 年 7 月號的《SQL Server Professional》。Copyright 2003，除了另外註明的項目以外，版權均屬 Pinnacle Publishing, Inc. 所有。All rights are reserved. 《SQL Server Professional》是 Pinnacle Publishing, Inc. 獨立製作的出版品。若無事先獲得 Pinnacle Publishing, Inc. 同意，禁止以任何形式使用或複製本文的任何部分 (在評論文章或評語中使用的簡短引述除外)。若要與 Pinnacle Publishing, Inc. 聯繫，請電洽 1-800-788-1900。