Solved

concatenate two TEXT fields in MSSQL 2000

Posted on 2009-03-30
6
779 Views
Last Modified: 2012-05-06
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
Comment
Question by:RPCIT
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 350 total points
ID: 24023255
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
 
LVL 22

Expert Comment

by:pivar
ID: 24023315
If you would like to concatenate a varchar variable use

UPDATETEXT AppendTest_Source.tData3 @ptr_tData3 NULL 0 @charvar



0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24023343
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 6

Author Comment

by:RPCIT
ID: 24023835
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 24023954
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
 
LVL 6

Author Closing Comment

by:RPCIT
ID: 31564423
The Pivar Solution worked, but Angel helped to add to my understanding.  Thanks for your help!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now