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

LVL 6
RPCITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pivarCommented:
Hi,

Try this, tData3 field mustn't be null, but blank.


/peter

DECLARE @ptr_tData1 varbinary(16)
DECLARE @ptr_tData2 varbinary(16)
DECLARE @ptr_tData3 varbinary(16)
 
SELECT  @ptr_tData1 = TEXTPTR(tData1), @ptr_tData2 = TEXTPTR(tData2), @ptr_tData3 = TEXTPTR(tData3)
FROM AppendTest_Source WHERE Source_Key = 1
 
UPDATETEXT  AppendTest_Source.tData3 @ptr_tData3 NULL 0 AppendTest_Source.tData1 @ptr_tData1
UPDATETEXT  AppendTest_Source.tData3 @ptr_tData3 NULL 0 AppendTest_Source.tData2 @ptr_tData2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pivarCommented:
If you would like to concatenate a varchar variable use

UPDATETEXT AppendTest_Source.tData3 @ptr_tData3 NULL 0 @charvar



0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

RPCITAuthor Commented:
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?


0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
take this line:
UPDATETEXT  t2.data @ptr_tData2 null null t1.data @ptr_tData1

the NULL NULL part in there will make that the data from @ptr_tData1 is APPENDED to the t2.Data.

so, your request is "simple:"
ensure tData3 is not null, but at least '' (empty string), and then call the UPDATETEXT for each value you want to append.

<table1>.<column> @ptr_tData1
if it's from a TEXT column from another table/row

or a plain varchar variable containing plain varchar data to append.
0
RPCITAuthor Commented:
The Pivar Solution worked, but Angel helped to add to my understanding.  Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.