TEXT类型字段相加的问题?

[moqing]

Create Table #Text_Temp(cDoAble text,cStatus Text,cEffect Text,cVote Text)
Insert into #Text_Temp (cDoAble,cStatus,cEffect,cVote) Values('','1','2','3')
Insert into #Text_Temp (cDoAble,cStatus,cEffect,cVote) Values('','A','B','C')
Insert into #Text_Temp (cDoAble,cStatus,cEffect,cVote) Values('','I','II','III')
Insert into #Text_Temp (cDoAble,cStatus,cEffect,cVote) Values('','!','@','#')

--------想实现如下的语句的效果-----------------------------------------
-- UPdate cDoAble With '[现状分析]'+ CHAR(13)+
-- +" "+Rtrim( cStatus )+CHAR(13)+'[目标效果分析]'+CHAR(13)+
-- +" "+Rtrim( cEffect )+CHAR(13)+'[建议及对策]'+CHAR(13)+
-- +" "+Rtrim( cVote )

[WangZWang]

--如果各字段内容实际长度未超过8000,可用转换实现,如:
.......................
declare @DoAble varchar(8000)
Select @DoAble=isNULL(@DoAble,'')+'[现状分析]'+ CHAR(13)+
+' '+Rtrim(cast(cStatus as varchar))+CHAR(13)+'[目标效果分析]'+CHAR(13)+
+' '+Rtrim(cast(cEffect as varchar))+CHAR(13)+'[建议及对策]'+CHAR(13)+
+' '+Rtrim(cast(cVote as varchar))
from #Text_Temp
print @DoAble

[zjcxc]

下面的代码演示, 如果把两个 text 列相加, 更新到其中一个 text 列中
USE tempdb
GO

-- 测试环境
CREATE TABLE ta(id int identity(1, 1), col1 text)
CREATE TABLE tb(id int identity(1, 1), col1 text)
INSERT ta SELECT REPLICATE('abc', 8000)
INSERT tb SELECT REPLICATE('cba', 8000)

-- show insert data length
SELECT [ta.col1] = DATALENGTH(col1) FROM ta
SELECT [tb.col1] = DATALENGTH(col1) FROM tb
GO

-- 更新 ta, 让 ta.col1 = ta.col1 + ' ' + tb.col1
-- 条件: ta.id = 1, tb.id = 1
DECLARE @p1 binary(16), @p2 binary(16)

SELECT @p1 = TEXTPTR(col1) FROM ta (NOLOCK)
WHERE id = 1
IF TEXTVALID('ta.col1', @p1) = 0
BEGIN
RAISERROR('ta.col1 invalid', 16, 1)
RETURN
END

SELECT @p2 = TEXTPTR(col1) FROM tb (NOLOCK)
WHERE id = 1
IF TEXTVALID('tb.col1', @p2) = 0
BEGIN
RAISERROR('tb.col1 invalid', 16, 1)
RETURN
END

-- insert space split
UPDATETEXT ta.col1 @p1 NULL 0 '-'

--insert tb.col1
UPDATETEXT ta.col1 @p1 NULL 0 tb.col1 @p2
GO

-- show update result
SELECT [ta.col1] = SUBSTRING(col1, 1, 10) + '...' + SUBSTRING(col1, 15988, 100),
[ta.col1_length]= DATALENGTH(col1)
FROM ta
WHERE id = 1
GO

DROP TABLE ta, tb

[hellowork]

学习...