示例
下例读取 pub_info 表中 pr_info 列的第 2 个至第 26 个字符。USE pubs
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1 25
GO
下例读取 pub_info 表中 pr_info 列的第 2 个至第 26 个字符。USE pubs
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1 25
GO
子字符串的开始位置。
要返回的字符串的长度(以字符数表示,对于 binary 类型以字节数表示)。
下面的示例显示了每个职员的名的首字母和姓,例如 A Fuller:USE Northwind
SELECT SUBSTRING(FirstName, 1, 1), LastName
FROM Employees下面的示例显示字符串常量 abcdef 的第二个、第三个和第四个字符:SELECT x = SUBSTRING('abcdef', 2, 3)x
----------
bcd(1 row(s) affected)
Create View View_表
as
select left(convert(varchar(8000),字段),20)+'......' as T from 表
2楼为什么我用left出错, SUBSTRING就可以
3楼的如果字段长度超过8000,估计会出错吧
Create View View_表
as
select column1=cast(SUBSTRING('字段名1',firstlength,lastlength) as varchar(50)),
column2=cast(SUBSTRING('字段名2',firstlength,lastlength) as varchar(50)),
.......,'
SET @vchExecMaster2 = '
column11=cast(SUBSTRING('字段名1',firstlength,lastlength) as varchar(50)),
column12=cast(SUBSTRING('字段名2',firstlength,lastlength) as varchar(50)),
.......'
from 表
EXEC (@vchExecMaster1 + @vchExecMaster2)
--PRINT @vchExecMaster1
--PRINT @vchExecMaster2
--PRINT 'GO'