Solved

concatenate two TEXT fields in MSSQL 2000

Posted on 2009-03-30
6
784 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 143

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…

733 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