asked on

concatenate two TEXT fields in MSSQL 2000

I am trying to concatenate two TEXT fields into a third field. In actuality, once I get this figured out, it will be a mixture of TEXT fields, varchars, and other types of fields, all being pushed into a TEXT field. But for the purposes of this question I decided to simplify the issue I'm having.

As you can see in the code I have attached, i've tried a few things to try to get tData1 and tData2 into tData3. (all three fields are type:text,null) And I'm lost with what to do next. And Yes.. I'm sorry it's SQL 2000, this would be much easier in 2005

DECLARE @ptr_tData1 varbinary(16)
DECLARE @ptr_tData2 varbinary(16)
DECLARE @dataLength1 int
DECLARE @dataLength2 int
SELECT	@ptr_tData1 = TEXTPTR(tData1), @dataLength1=DATALENGTH(tData1), 
		@ptr_tData2 = TEXTPTR(tData2), @dataLength2=DATALENGTH(tData2) 
FROM AppendTest_Source WHERE Source_Key = 1
READTEXT AppendTest_Source.tData1 @ptr_tData1 0 @dataLength1
READTEXT AppendTest_Source.tData2 @ptr_tData2 0 @dataLength2
--DECLARE @ptr_out varbinary(16)
DECLARE @ptr_tData3 varbinary(16)
DECLARE @dataLength3 int
SELECT  @ptr_tData3 = TEXTPTR(tData3), @dataLength3=DATALENGTH(tData3)
FROM AppendTest_Source WHERE Source_Key = 1
--SET @ptr_out =  @ptr_tData1 + @ptr_tData2
WRITETEXT AppendTest_Source.tData3 @ptr_tData3 
--UPDATETEXT  AppendTest_Source.tData3 @ptr_tData3 @dataLength1 @dataLength2 @ptr_tData2

Open in new window

ASKER CERTIFIED SOLUTION

pivar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pivar

If you would like to concatenate a varchar variable use

UPDATETEXT AppendTest_Source.tData3 @ptr_tData3 NULL 0 @charvar

Guy Hengel [angelIII / a3]

it shall be very easy, actually.

see this script:

create table t1 ( id int identity, data text )
go
create table t2 ( id int identity, data text )
go
insert into t1 (data) values ( 'lkmsdfljlj j kjajf lsjflkjpj3 wpet g99wq ire wjfsldmnf snfmn w1qj34r23pjr pwjefk n')
insert into t2 (data) values ( '123 ')
go
 
DECLARE @ptr_tData1 varbinary(16)
DECLARE @dataLength1 int
 
DECLARE @ptr_tData2 varbinary(16)
DECLARE @dataLength2 int
 
SELECT  @ptr_tData1 = TEXTPTR(data), @dataLength1=DATALENGTH(data)
FROM t1 WHERE id = 1
 
SELECT  @ptr_tData2 = TEXTPTR(data), @dataLength2=DATALENGTH(data)
FROM t2 WHERE id = 1
 
UPDATETEXT  t2.data @ptr_tData2 null null t1.data @ptr_tData1
 
 
go
select id, datalength(data), data from t1
select id, datalength(data), data from t2
 
go
drop table t2
go
drop table t1

Open in new window

RPCIT

ASKER

Thank you for the replies,

Angel, Pivar:
I don't think I stated my need very well,

tData1 and tData2 need to be contatenated, and placed into tdata3.

for instance.. if tData1 is "abcd" and tData2 is "efgh", I need tData3 to be "abcdefgh". Except that each of those values can be well over 175,000 characters.

Peter: Wouldn't that just set the value of tData3 to tData1, then reset it to tData2?

SOLUTION