Help with Text Field "concatenation"

Question

Post reply

Help with Text Field "concatenation"

Marc-140120

Grasshopper

Points: 10
More actions
April 23, 2004 at 12:25 pm

#85057

Hello, I'm relatively new to SQL Server (but have been using oracle for many years) and was hoping someone could give me a hand working with text fields.
what i'm trying to do is create a summary table joining multiple tables together and put the information into a single row. the problem i'm running into is a column in a table (called comments) that has a text variable. basically for a given ID, i want to loop thru the comments table via a cursor i assume and pick up all the entries (comments) and concatenate them into one text field, seperating them by a datestamp.
for example for ID 123, that has say 3 comments (or 3 rows in the comment table) output that information into my new table that also has a text column called CommentSum. so it would be like '[01/01/2004]' + "comment 1 ...." + [01/05/2004]' + "comment 2 ...." + [01/15/2004] + "comment 3 ...."
i've read up on how you need to get the pointer of the text field for reading/updating/inserting and that you can not define a local variable as a text field in a stored procedure, but you can define an input parameter of a stored proc as a text field. this is where i'm kinda stuck at this point.
was wondering if anyone could shed a little light on how i would accomplish this and sample could would be extremely helpful.
thanks for any help, marc

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Mark,

The only thing I can think of (down and dirty) is to either

A) CAST(TextField AS VARCHAR(8000))

or

B) CONVERT(VARCHAR(8000), TextField)

and then concat it the above together

Good Luck

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Marc-140120 Grasshopper Points: 10 More actions · Answer 2

thanks. i've done that actually, the only problem with that is about 5% of the rows have entries that exceed 8000 characters and they then get truncated. pissed me off when i saw that

any other options are truly welcome!

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

hmmm, how about

DECLARE @text1 VARCHAR(8000)

DECLARE @text2 VARCHAR(8000)

SET @text1 = LEFT(textfield, 8000)

CASE WHEN LEN(textfield) > 8000

THEN SET @text2 = SUBSTRING(textfield, 8001, 8000)

ELSE SET @text2 = ''

CONCAT @text1 + @text2 and so forth????

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Marc-140120 Grasshopper Points: 10 More actions · Answer 4

that could get me closer. do you know how to do a length test on a text field? len(TextVariable) doesn't work. ugh.

this way i could test them all and see how many varchar fields i need to set up before hand.

thanks again

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

Sorry on that one Marc. I don't work much with text fields. The only thing I could think of is set-up three VARCHAR(8000) variables and just re-use them for each one of your text fields. This way you have 24,000 chars and hopefully don't have anything longer....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

u2kota SSChasing Mays Points: 601 More actions · Answer 6

Hi Marc,

You can use DataLength function to get the length of data stored in text fields.

Cheers...

Phani

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Here's a sample that may help

create table results ([ID] int, result text)

create table comments ([ID] int, CID int, CDate datetime, comment text)

insert into results values (123,'')

insert into comments values (123,1,'2004-01-01','comment 1')

insert into comments values (123,2,'2004-01-05','comment 2')

insert into comments values (123,3,'2004-01-15','comment 3')

DECLARE @ptr1 varbinary(16), @ptr2 varbinary(16), @counter int, @date char(12)

SELECT @ptr1 = TEXTPTR(result)

FROM results

WHERE [ID] = 123

SET @counter = 0

WHILE (@counter < 3)

BEGIN

SET @counter = @counter + 1

SELECT @ptr2 = TEXTPTR(comment)

FROM comments

WHERE [ID] = 123

AND CID = @counter

SELECT @date = '['+CONVERT(char(10),CDate,101)+']'

FROM comments

WHERE [ID] = 123

AND CID = @counter

UPDATETEXT results.result @ptr1 NULL 0 @date

UPDATETEXT results.result @ptr1 NULL 0 comments.comment @ptr2

END

select * from results

select * from comments

drop table results

drop table comments

Far away is close at hand in the images of elsewhere.
Anon.

Marc-140120 Grasshopper Points: 10 More actions · Answer 8

great. thank you very much. will try this one!

Marc-140120 Grasshopper Points: 10 More actions · Answer 9

thank you David! i modified this to match what i'm doing and it worked perfect! thank you very much again, this is a big weight lifted off my shoulders!

all the best...