January 17, 2008 at 10:04 am
I've got a problem that I can't seem to figure out. I need to concatinate two text fields from a table and insert the result into a text field in a different table. I can concatinate a varchar to the text using updatetext, however can't get it to work with 2 text fields. Does anyone have any ideas on how to do this?
Thanks
January 17, 2008 at 10:14 am
I may be misunderstanding your question....but wouldn't it just be
Declare newText as varchar(1000)
SELECT
SET newText = Field1 + Field2
FROM
textTable
INSERT INTO otherTable
(textfield)
VALUES
(newText)
I'm a newb myself, so I may be way off
January 17, 2008 at 10:17 am
You can't use the + operator on text datatypes.
January 17, 2008 at 10:30 am
Hello,
Can you test this sample at your end?
Create table a (dt text, dt1 text)
insert into a values ('this is to', 'test the concatenation')
select * from a
create table b (dt2 text)
insert into b
select convert(varchar, dt) + ' ' + convert(varchar, dt1)
from a
select * from b
Lucky
January 17, 2008 at 10:37 am
That works, however the main problem that i have, which I neglected to put in the original message, is that the data in my text fields is larger than 8000 chars. So my resulting data is truncated.
lucky (1/17/2008)
Hello,Can you test this sample at your end?
Create table a (dt text, dt1 text)
insert into a values ('this is to', 'test the concatenation')
select * from a
create table b (dt2 text)
insert into b
select convert(varchar, dt) + ' ' + convert(varchar, dt1)
from a
select * from b
January 17, 2008 at 10:46 am
Use varchar(Max)
convert(varchar(max), dt) + ' ' + convert(varchar(max), dt1)
January 17, 2008 at 11:10 am
theres a couple problems with that.
The 1st is that I'm on SQL2000. The second is that in one of my text columns there are over 43000 charactors. So even if i did a varchar(8000), my data would still be truncated
Adam Haines (1/17/2008)
Use varchar(Max)convert(varchar(max), dt) + ' ' + convert(varchar(max), dt1)
January 17, 2008 at 11:35 am
You'll need to use the WRITETEXT function to do that. It's a little complex, with pointers and such, but it can be done. Books Online gives the details. Look up WRITETEXT in there, look up how to create text pointers at the same time (also in BOL).
You might have to do it one row at a time, I don't remember. Might be able to do it set-based, but I'm not sure if text pointers work that way.
If you have questions after reading about in BOL, please let me know and I'll help further. I haven't worked with text fields in a year or so, but I used to do it a lot and I'm sure I can make myself remember how it works. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 11:35 am
Sorry, I assumed that you were using SQL 2005 because this is posted in the SQL 2005 forum. In SQL Server 2005 a varchar(max) can hold up to 2GB of data which is what I would have recommend your concatenated text column be changed too.
You are going to have to use
Writetext or updatetext
more info:
http://technet.microsoft.com/en-us/library/ms186838.aspx
There is a maximum limitation of 120 KB length for writetext insert; I do not know if this will affect you or not.
January 17, 2008 at 11:36 am
You'll need to use the WRITETEXT function to do that. It's a little complex, with pointers and such, but it can be done. Books Online gives the details. Look up WRITETEXT in there, look up how to create text pointers at the same time (also in BOL).
You might have to do it one row at a time, I don't remember. Might be able to do it set-based, but I'm not sure if text pointers work that way.
If you have questions after reading about in BOL, please let me know and I'll help further. I haven't worked with text fields in a year or so, but I used to do it a lot and I'm sure I can make myself remember how it works.
Beat me to it
January 17, 2008 at 11:44 am
I've used both writetext and updatetext, however they expect a varchar value as the last parameter. What i really want to do is use updatetext, but instead of passing a string as the text to append, i want to append data from a text datatype field in the database.
Adam Haines (1/17/2008)
You'll need to use the WRITETEXT function to do that. It's a little complex, with pointers and such, but it can be done. Books Online gives the details. Look up WRITETEXT in there, look up how to create text pointers at the same time (also in BOL).
You might have to do it one row at a time, I don't remember. Might be able to do it set-based, but I'm not sure if text pointers work that way.
If you have questions after reading about in BOL, please let me know and I'll help further. I haven't worked with text fields in a year or so, but I used to do it a lot and I'm sure I can make myself remember how it works.
Beat me to it
January 17, 2008 at 12:02 pm
jkurtis (1/17/2008)
I've used both writetext and updatetext, however they expect a varchar value as the last parameter. What i really want to do is use updatetext, but instead of passing a string as the text to append, i want to append data from a text datatype field in the database.
In that case, you'll have to break up the second text field into chunks that will fit in a varchar(8000) field.
Create a temp table with the second text field broken into chunks.
create table #Text2 (Seq int primary key, VC as varchar(8000))
insert into #text2(Seq, VC)
select 1, substring(text_field_2, 0, 8000)
from dbo.table_with_text_field_2
union all
select 2, substring(text_field_2, 8001, 8000)
from dbo.table_with_text_field_2
union all
select 3, substring(text_field_2, 16001, 8000)
from dbo.table_with_text_field_2
...
You can do this as a While loop (set the begin value as a variable, increment by 8000, and run the While as @StartOfSubstring < @LengthOfSubstring; assuming you use those variable names), or you can do it with a Union All and manually set the numbers.
Then, step through the temp table and add it to the first text field in the target table one VC field at a time. (I picked VC for "VarChar". Use whatever you like for the name.)
It's not pretty, and it violates the "don't step through, use sets" rule, but it will work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 12:11 pm
yah i was afraid of that. Im afraid that with the data I need to loop through that would take a while. One text field has over 43000 chars in it.
GSquared (1/17/2008)
jkurtis (1/17/2008)
I've used both writetext and updatetext, however they expect a varchar value as the last parameter. What i really want to do is use updatetext, but instead of passing a string as the text to append, i want to append data from a text datatype field in the database.In that case, you'll have to break up the second text field into chunks that will fit in a varchar(8000) field.
Create a temp table with the second text field broken into chunks.
create table #Text2 (Seq int primary key, VC as varchar(8000))
insert into #text2(Seq, VC)
select 1, substring(text_field_2, 0, 8000)
from dbo.table_with_text_field_2
union all
select 2, substring(text_field_2, 8001, 8000)
from dbo.table_with_text_field_2
union all
select 3, substring(text_field_2, 16001, 8000)
from dbo.table_with_text_field_2
...
You can do this as a While loop (set the begin value as a variable, increment by 8000, and run the While as @StartOfSubstring < @LengthOfSubstring; assuming you use those variable names), or you can do it with a Union All and manually set the numbers.
Then, step through the temp table and add it to the first text field in the target table one VC field at a time. (I picked VC for "VarChar". Use whatever you like for the name.)
It's not pretty, and it violates the "don't step through, use sets" rule, but it will work.
January 17, 2008 at 3:04 pm
43,000 is only 6 iterations through at 8,000/iteration. It's not elegant, it's even ugly, but it shouldn't take too awefully long to accomplish.
Good luck and let us know how it goes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 9:10 pm
jkurtis (1/17/2008)
theres a couple problems with that.The 1st is that I'm on SQL2000.
And people wonder why I get ticked at some users... what were you thinking when you posted this on a 2005 forum without saying you were using 2k in advance? You wasted a bunch of people's time...
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply