• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

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

0
RPCIT
Asked:
RPCIT
  • 2
  • 2
  • 2
2 Solutions
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now