Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

concatenate two TEXT fields in MSSQL 2000

Posted on 2009-03-30
6
Medium Priority
?
791 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 1400 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 600 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

916 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