在 text、ntext 和 image 数据上使用 SUBSTRING 下例显示如何从 pubs 数据库的 publishers 表内的每个 text 和 image 数据列中返回前 200 个字符。text 数据以 varchar 的形式返回,image 数据则以 varbinary 的形式返回。USE pubs SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, SUBSTRING(pr_info, 1, 10) AS pr_info FROM pub_info WHERE pub_id = '1756'下面是结果集:pub_id logo pr_info ------ ---------------------- ---------- 1756 0x474946383961E3002500 This is sa(1 row(s) affected)下例显示 SUBSTRING 在 text 和 ntext 数据上的效果。首先,下例在 pubs 数据库内创建一个名为 npr_info 的新表。然后,在 npr_info 表中用 pub_info.pr_info 列的前 80 个字符创建 pr_info 列,并添加ü作为首字符。最后,INNER JOIN 检索所有出版商标识号以及 text 和 ntext 出版商信息列的 SUBSTRING。IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'npub_info') DROP TABLE npub_info GO -- Create npub_info table in pubs database. Borrowed from instpubs.sql. USE pubs GO CREATE TABLE npub_info ( pub_id char(4) NOT NULL REFERENCES publishers(pub_id) CONSTRAINT UPKCL_npubinfo PRIMARY KEY CLUSTERED, pr_info ntext NULL )GO-- Fill the pr_info column in npub_info with international data. RAISERROR('Now at the inserts to pub_info...',0,1)GOINSERT npub_info VALUES('0736', N'üThis is sample text data for New Moon Books, publisher 0736 in the pubs database') INSERT npub_info values('0877', N'üThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa') INSERT npub_info values('1389', N'üThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs da') INSERT npub_info values('9952', N'üThis is sample text data for Scootney Books, publisher 9952 in the pubs database') INSERT npub_info values('1622', N'üThis is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d') INSERT npub_info values('1756', N'üThis is sample text data for Ramona Publishers, publisher 1756 in the pubs datab') INSERT npub_info values('9901', N'üThis is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G i') INSERT npub_info values('9999', N'üThis is sample text data for Lucerne Publishing, publisher 9999 in the pubs data') GO -- Join between npub_info and pub_info on pub_id. SELECT pr.pub_id, SUBSTRING(pr.pr_info, 1, 35) AS pr_info, SUBSTRING(npr.pr_info, 1, 35) AS npr_info FROM pub_info pr INNER JOIN npub_info npr ON pr.pub_id = npr.pub_id ORDER BY pr.pub_id ASC
下例显示如何从 pubs 数据库的 publishers 表内的每个 text 和 image 数据列中返回前 200 个字符。text 数据以 varchar 的形式返回,image 数据则以 varbinary 的形式返回。USE pubs
SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo,
SUBSTRING(pr_info, 1, 10) AS pr_info
FROM pub_info
WHERE pub_id = '1756'下面是结果集:pub_id logo pr_info
------ ---------------------- ----------
1756 0x474946383961E3002500 This is sa(1 row(s) affected)下例显示 SUBSTRING 在 text 和 ntext 数据上的效果。首先,下例在 pubs 数据库内创建一个名为 npr_info 的新表。然后,在 npr_info 表中用 pub_info.pr_info 列的前 80 个字符创建 pr_info 列,并添加ü作为首字符。最后,INNER JOIN 检索所有出版商标识号以及 text 和 ntext 出版商信息列的 SUBSTRING。IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'npub_info')
DROP TABLE npub_info
GO
-- Create npub_info table in pubs database. Borrowed from instpubs.sql.
USE pubs
GO
CREATE TABLE npub_info
(
pub_id char(4) NOT NULL
REFERENCES publishers(pub_id)
CONSTRAINT UPKCL_npubinfo PRIMARY KEY CLUSTERED,
pr_info ntext NULL
)GO-- Fill the pr_info column in npub_info with international data.
RAISERROR('Now at the inserts to pub_info...',0,1)GOINSERT npub_info VALUES('0736', N'üThis is sample text data for New Moon Books, publisher 0736 in the pubs database')
INSERT npub_info values('0877', N'üThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa')
INSERT npub_info values('1389', N'üThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs da')
INSERT npub_info values('9952', N'üThis is sample text data for Scootney Books, publisher 9952 in the pubs database')
INSERT npub_info values('1622', N'üThis is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d')
INSERT npub_info values('1756', N'üThis is sample text data for Ramona Publishers, publisher 1756 in the pubs datab')
INSERT npub_info values('9901', N'üThis is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G i')
INSERT npub_info values('9999', N'üThis is sample text data for Lucerne Publishing, publisher 9999 in the pubs data')
GO
-- Join between npub_info and pub_info on pub_id.
SELECT pr.pub_id, SUBSTRING(pr.pr_info, 1, 35) AS pr_info,
SUBSTRING(npr.pr_info, 1, 35) AS npr_info
FROM pub_info pr INNER JOIN npub_info npr
ON pr.pub_id = npr.pub_id
ORDER BY pr.pub_id ASC
---
0xA1B0A1B1BAC5BACDA1B6A1B700000000000000000000000000000000000000000(所影响的行数为 1 行)
*/