How to concate 2 ore more text fields into one field?

Hello,

I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table.

Something like - Note: the codes below doesn't work!!

DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE _rows CURSOR
FOR SELECT AccID FROM tableA

FETCH NEXT FROM _rows INTO @.AccID

WHILE (@.@.fetch_status <> -1)
BEGIN

UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @.AccID)

WHERE AccID = @.AccID

FETCH NEXT FROM _rows INTO @.AccID

END

Thanks in advance

I tested something like this:(Assume you are using SQL Server 2000 text fields)

You can try it out in your code.

DECLARE @.ptr varbinary(50),@.ptr2 varbinary(50)

DECLARE @.len int, @.len2 int

update Table_B SET colB=(SELECT colA FROM Table_A where Accid=1) WHERE Accid=1

select @.ptr = TEXTPTR(colB), @.len=datalength(colB), @.ptr2 = TEXTPTR(report), @.len2=datalength(report) from Table_B WHERE Accid=1

UPDATETEXT Table_B.report @.ptr2 @.len2 0 Table_B.colB @.ptr

In SQL Server 2005, you can define Varchar(MAX) field, it will be a lot more easy to manipulate by using .WRITE function.

|||

Thanks Limno,

Sorry I could reply this sooner. This is one slick trick - appreciated.

However, I couldn't get this working as the way you have. Not sure what I am missing.

I kept having the error

Server: Msg 7116, Level 16, State 4, Line 13
Offset 17 is not in the range of available text, ntext, or image data.
The statement has been terminated.

DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE @.Heading varchar(255)
DECLARE @.ptrReport varbinary(16)
DECLARE @.ptrTmpField varbinary(16)
DECLARE @.Len1 int, @.Len2 int

SELECT @.ptrTmpField = TEXTPTR(tmpField), @.Len1 = Datalength(tmpField),
@.ptrReport = TEXTPTR(Report), @.len2 = Datalength(report)
FROM PHILTBL2 WHERE AccID = 1624728

UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 tmpField @.ptrTmpField

Thanks

|||

If I changed the updatetext statement as

UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 @.ptrTmpField

The error went away, but I get gebrish text in my report field.

Look like the textptr doesn't work

|||

Check this link that Jared Ko provided earlier today:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

|||

Thanks CetinBasoz, but the the problem I am dealing with is the text field and it's a different animal than varchar field. :-(

Check here for more info

http://msdn2.microsoft.com/en-us/library/ms189466.aspx

|||

Please see below scripts for some examples on how to use UPDATETEXT with TEXTPTR.

-- Script #1

/* To concatenate several binary values into one image field: */
create table #Bin ( SegmentID int IDENTITY ( 1 , 1 ) , Segment varbinary(2) )
insert #Bin values (0x11)
insert #Bin values (0x22)
select * from #Bin
go
declare @.imageptr varbinary(16), @.segmentid int, @.segment varbinary(16)
create table #AllBin ( Segments image null )
-- Get valid pointer first
insert #AllBin values ( 0x0 )
select @.imageptr = TEXTPTR( Segments ) from #AllBin
-- Set data to null
update #AllBin set Segments = null

select @.segmentid = -1
while(1=1)
begin
select @.segmentid = min(segmentid)
from #Bin
where segmentid > @.segmentid
if @.segmentid is null break

select @.segment = segment from #Bin where segmentid = @.segmentid

updatetext #AllBin.Segments @.imageptr null 0 @.segment
end
select * from #AllBin
go
drop table #Bin
drop table #AllBin
go

-- Script #2

create procedure #t (
@.t1 text , @.i1 image, @.t2 text, @.i2 image, @.t3 varchar(30), @.i3 varbinary(2)
)
as
declare @.tptr varbinary(16), @.iptr varbinary(16), @.tpos int, @.ipos int
create table #blob(id int identity, t text, i image)

insert #blob values(@.t1, @.i1)
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

update #blob set t = @.t2, i = @.i2 where id = @.@.identity
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

select @.tptr = TEXTPTR(t), @.tpos = PATINDEX('%TEXT...%', t) - 1,
@.iptr = TEXTPTR(i), @.ipos = 2
from #blob

updatetext #blob.t @.tptr @.tpos 0 @.t3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

updatetext #blob.i @.iptr @.ipos 1 @.i3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
go

exec #t 'SOME TEXT HERE...', 0x02498765bcde3,
'MODIFIED TEXT...', 0xab86ec64,
'(INSERT BEFORE TEXT) ', 0xcd -- replace 3rd byte

-- Inserted text & image value
/* id text_val image_val
-- -- --
1 SOME TEXT HERE... 0x002498765BCDE3
*/

-- Updated text & image value. This one replaces the existing values
/*
id text_val image_val
-- -- --
1 MODIFIED TEXT... 0xAB86EC64
*/

-- Modified text value only. This one inserts some text into the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86EC64
*/

-- Modified image value only. This one changes a byte in the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86CD64

*/
go
drop proc #t
go

Also, here is a link to a SP that show how to generate text value from multiple strings.

|||Sorry my bad. When I wrote it was around 3 AM here.