SQL语句中如何获取为text类型的字段？

Question

在SQLServer2005中，写sql语句时，如何获取text类型的字段。我想把一个text类型的字段temp（温度列。有多个。用逗号隔开）获取到之后在传给一个分割函数获得具体的某个温度。现求：如何获取text类型的字段？

Answer 1

为难你自己，用VARCHAR(MAX)够了，为何刚开始要合在一起呢？现在又费力给拆分

Answer 2

举个例子.create table tab6 (P varchar(10), S varchar(10))insert into tab6
select 'P1','S1,S2' union all
select 'P2','S3'select * from tab6P          S
---------- ----------
P1         S1,S2
P2         S3select a.p,substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) s
from tab6 a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.s)
   and substring(','+a.s,b.number,1) = ','p          s
---------- ----------
P1         S1
P1         S2
P2         S3

Answer 3

我建议你改字段类型为varchar.
拆分可参考如下:
/*
标题：简单数据拆分(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
描述：有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/--1. 旧的解决方法(sql server 2000)create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go--方法1.使用临时表完成
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--方法2.如果数据量小，可不使用临时表
select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) 
from tb a join master..spt_values  b 
on b.type='p' and b.number between 1 and len(a.value)
where substring(',' + a.value , b.number , 1) = ','--2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go--方法1.使用xml完成
SELECT A.id, B.value FROM
(
  SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
) A OUTER APPLY
(
  SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
) B--方法2.使用CTE完成
;with tt as 
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)
DROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 4

create table t1(id int,col1 text)
create table t2(id int,col2 text)
go
select * from syscolumns where xtype=35
/*
name                                                                                                                             id          xtype typestat xusertype length xprec xscale colid  xoffset bitpos reserved colstat cdefault    domain      number colorder autoval                                                                                                                                                                                                                                                            offset collationid language    status type usertype printfmt                                                                                                                                                                                                                                                        prec   scale       iscomputed  isoutparam  isnullable  collation                                                                                                                        tdscollation
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- -------- --------- ------ ----- ------ ------ ------- ------ -------- ------- ----------- ----------- ------ -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------ ----------- ----------- ------ ---- -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ----------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ------------
col1                                                                                                                             742293704   35    0        35        16     0     0      2      0       0      0        0       0           0           0      2        NULL                                                                                                                                                                                                                                                               -2     53284       0           8      35   19       NULL                                                                                                                                                                                                                                                            NULL   NULL        0           0           1           Chinese_PRC_CI_AS                                                                                                                0x0408D00000
col2                                                                                                                             758293761   35    0        35        16     0     0      2      0       0      0        0       0           0           0      2        NULL                                                                                                                                                                                                                                                               -2     53284       0           8      35   19       NULL                                                                                                                                                                                                                                                            NULL   NULL        0           0           1           Chinese_PRC_CI_AS                                                                                                                0x0408D00000(2 行受影响)
*/
go
drop table t1,t2

Answer 5

数据库不是我写的。别人已经写好了。我只是调用数据库的数据而已。由于远程获取数据。且数据很大。所以写SQL逻辑代码进行获取。现在问题是不知道怎么获取text类型的字段里的值给分割函数。求帮助

Answer 6

数据库已经写死不能改了。而且我也没权限动。只是调去数据而已。我是远程获取。且数据很大。现在问题是获取text类型的值放到分割函数里面去获得我想要的数据。现求：如何获取text类型的字段里的值？