网站 › 『 Sybase Other 』 › [求助]如何更新表中TEXT类型字段的值

TIMMY 发表于 2008-3-6 22:43:18

[求助]如何更新表中TEXT类型字段的值

<p><font face="Verdana">两个TEXT字段的内容相加</font></p>
<p><font face="Verdana">用WRITETEXT只能覆盖数据，</font></p>
<p><font face="Verdana">updatetext 则不存在！！</font></p>
<p>&nbsp;</p>

ehxz 发表于 2008-3-7 11:03:14

<p>CSDN上看来的东东：</p>
<p>Create &nbsp; Table &nbsp; #Text_Temp(cDoAble &nbsp; text,cStatus &nbsp; Text,cEffect &nbsp; Text,cVote &nbsp; Text) &nbsp; <br/>&nbsp; Insert &nbsp; into &nbsp; &nbsp; #Text_Temp &nbsp; (cDoAble,cStatus,cEffect,cVote) &nbsp; Values('','1','2','3') &nbsp; <br/>&nbsp; Insert &nbsp; into &nbsp; &nbsp; #Text_Temp &nbsp; (cDoAble,cStatus,cEffect,cVote) &nbsp; Values('','A','B','C') &nbsp; <br/>&nbsp; Insert &nbsp; into &nbsp; &nbsp; #Text_Temp &nbsp; (cDoAble,cStatus,cEffect,cVote) &nbsp; Values('','I','II','III') &nbsp; <br/>&nbsp; Insert &nbsp; into &nbsp; &nbsp; #Text_Temp &nbsp; (cDoAble,cStatus,cEffect,cVote) &nbsp; Values('','!','@','#') &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; --------想实现如下的语句的效果----------------------------------------- &nbsp; <br/>&nbsp; -- &nbsp; UPdate &nbsp; &nbsp; cDoAble &nbsp; &nbsp; With &nbsp; '[现状分析]'+ &nbsp; CHAR(13)+ &nbsp; &nbsp; <br/>&nbsp; -- +" &nbsp; &nbsp; "+Rtrim( &nbsp; cStatus &nbsp; )+CHAR(13)+'[目标效果分析]'+CHAR(13)+ &nbsp; <br/>&nbsp; -- +" &nbsp; &nbsp; "+Rtrim( &nbsp; cEffect &nbsp; )+CHAR(13)+'[建议及对策]'+CHAR(13)+ &nbsp; <br/>&nbsp; -- +" &nbsp; &nbsp; "+Rtrim( &nbsp; cVote &nbsp; ) </p>
<p>&nbsp;</p>
<p>A：</p>
<p>--如果各字段内容实际长度未超过8000,可用转换实现,如: &nbsp; <br/>&nbsp; ....................... &nbsp; <br/>&nbsp; declare &nbsp; @DoAble &nbsp; varchar(8000) &nbsp; <br/>&nbsp; Select &nbsp; @DoAble=isNULL(@DoAble,'')+'[现状分析]'+ &nbsp; CHAR(13)+ &nbsp; &nbsp; <br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +' &nbsp; &nbsp; '+Rtrim(cast(cStatus &nbsp; as &nbsp; varchar))+CHAR(13)+'[目标效果分析]'+CHAR(13)+ &nbsp; <br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +' &nbsp; &nbsp; '+Rtrim(cast(cEffect &nbsp; as &nbsp; varchar))+CHAR(13)+'[建议及对策]'+CHAR(13)+ &nbsp; <br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +' &nbsp; &nbsp; '+Rtrim(cast(cVote &nbsp; as &nbsp; varchar)) &nbsp; <br/>&nbsp; from &nbsp; #Text_Temp &nbsp; <br/>&nbsp; print &nbsp; @DoAble</p>
<p>B：</p>下面的代码演示, &nbsp; 如果把两个 &nbsp; text &nbsp; 列相加, &nbsp; 更新到其中一个 &nbsp; text &nbsp; 列中 &nbsp; <br/>&nbsp; USE &nbsp; tempdb &nbsp; <br/>&nbsp; GO &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; -- &nbsp; 测试环境 &nbsp; <br/>&nbsp; CREATE &nbsp; TABLE &nbsp; ta(id &nbsp; int &nbsp; identity(1, &nbsp; 1), &nbsp; col1 &nbsp; text) &nbsp; <br/>&nbsp; CREATE &nbsp; TABLE &nbsp; tb(id &nbsp; int &nbsp; identity(1, &nbsp; 1), &nbsp; col1 &nbsp; text) &nbsp; <br/>&nbsp; INSERT &nbsp; ta &nbsp; SELECT &nbsp; REPLICATE('abc', &nbsp; 8000) &nbsp; <br/>&nbsp; INSERT &nbsp; tb &nbsp; SELECT &nbsp; REPLICATE('cba', &nbsp; 8000) &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; -- &nbsp; show &nbsp; insert &nbsp; data &nbsp; length &nbsp; <br/>&nbsp; SELECT &nbsp; &nbsp; = &nbsp; DATALENGTH(col1) &nbsp; FROM &nbsp; ta &nbsp; <br/>&nbsp; SELECT &nbsp; &nbsp; = &nbsp; DATALENGTH(col1) &nbsp; FROM &nbsp; tb &nbsp; <br/>&nbsp; GO &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; -- &nbsp; 更新 &nbsp; ta, &nbsp; 让 &nbsp; ta.col1 &nbsp; = &nbsp; ta.col1 &nbsp; + &nbsp; ' &nbsp; ' &nbsp; + &nbsp; tb.col1 &nbsp; <br/>&nbsp; -- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 条件: &nbsp; ta.id &nbsp; = &nbsp; 1, &nbsp; tb.id &nbsp; = &nbsp; 1 &nbsp; <br/>&nbsp; DECLARE &nbsp; @p1 &nbsp; binary(16), &nbsp; @p2 &nbsp; binary(16) &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; SELECT &nbsp; @p1 &nbsp; = &nbsp; TEXTPTR(col1) &nbsp; FROM &nbsp; ta &nbsp; (NOLOCK) &nbsp; <br/>&nbsp; WHERE &nbsp; id &nbsp; = &nbsp; 1 &nbsp; <br/>&nbsp; IF &nbsp; TEXTVALID('ta.col1', &nbsp; @p1) &nbsp; = &nbsp; 0 &nbsp; <br/>&nbsp; BEGIN &nbsp; <br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RAISERROR('ta.col1 &nbsp; invalid', &nbsp; 16, &nbsp; 1) &nbsp; <br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RETURN &nbsp; <br/>&nbsp; END &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; SELECT &nbsp; @p2 &nbsp; = &nbsp; TEXTPTR(col1) &nbsp; FROM &nbsp; tb &nbsp; (NOLOCK) &nbsp; <br/>&nbsp; WHERE &nbsp; id &nbsp; = &nbsp; 1 &nbsp; <br/>&nbsp; IF &nbsp; TEXTVALID('tb.col1', &nbsp; @p2) &nbsp; = &nbsp; 0 &nbsp; <br/>&nbsp; BEGIN &nbsp; <br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RAISERROR('tb.col1 &nbsp; invalid', &nbsp; 16, &nbsp; 1) &nbsp; <br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RETURN &nbsp; <br/>&nbsp; END &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; -- &nbsp; insert &nbsp; space &nbsp; split &nbsp; <br/>&nbsp; UPDATETEXT &nbsp; ta.col1 &nbsp; @p1 &nbsp; NULL &nbsp; 0 &nbsp; '-' &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; --insert &nbsp; tb.col1 &nbsp; <br/>&nbsp; UPDATETEXT &nbsp; ta.col1 &nbsp; @p1 &nbsp; NULL &nbsp; 0 &nbsp; tb.col1 &nbsp; @p2 &nbsp; <br/>&nbsp; GO &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; -- &nbsp; show &nbsp; update &nbsp; result &nbsp; <br/>&nbsp; SELECT &nbsp; &nbsp; = &nbsp; SUBSTRING(col1, &nbsp; 1, &nbsp; 10) &nbsp; + &nbsp; '...' &nbsp; + &nbsp; SUBSTRING(col1, &nbsp; 15988, &nbsp; 100), &nbsp; &nbsp; <br/>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = &nbsp; DATALENGTH(col1) &nbsp; <br/>&nbsp; FROM &nbsp; ta &nbsp; <br/>&nbsp; WHERE &nbsp; id &nbsp; = &nbsp; 1 &nbsp; <br/>&nbsp; GO &nbsp; <br/>&nbsp; &nbsp; <br/>&nbsp; DROP &nbsp; TABLE &nbsp; ta, &nbsp; tb&nbsp;&nbsp; <br/>
<p>&nbsp;</p>

TIMMY 发表于 2008-3-7 16:44:47

<p>在SYBASE环境中可以实现吗，我的text字符超过8000的，</p>
<p>sybase中UPDATETEXT&nbsp;可用吗，</p>
<p>&nbsp;</p>
<p>&nbsp;</p>

查看完整版本: [求助]如何更新表中TEXT类型字段的值