Using Char(13) to insert a return in a note field.

Question

Using Char(13) to insert a return in a note field.

myschif

Say Hey Kid

Points: 695
More actions
January 12, 2005 at 11:47 am

#66434

I am consolidating 4 comment fields into one note field (Record_Notes which is a text field). I would like to use the CHAR(13) to insert a return between each of the comments. Here is what I have:
CHAR(13) + 'Comment 1' + ': ' + rtrim(so.comments_1), + CHAR(13) + 'Comment 2' + ': ' + rtrim(so.comments_2), + CHAR(13) + 'Comment 3' + ': ' + rtrim(so.comments_3), + CHAR(13) + 'Comment 4' +': ' + rtrim(so.comments_4) Record_Notes
When I use the select part of the statement with results set to test,it looks good.

However, when I attempt to run the insert statement, it returns: "The select list for the INSERT statement contains more items than the insert list."
Apparently it sees the char(13) as individual columns rather than lines in the Record_Notes column I need them to be.
Any input is appreciated.

"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This is a shot in the dark.. but have you tried this :

CHAR(13) + 'Comment 1' + ': ' + rtrim(so.comments_1) + CHAR(13) + 'Comment 2' + ': ' + rtrim(so.comments_2) + CHAR(13) + 'Comment 3' + ': ' + rtrim(so.comments_3) + CHAR(13) + 'Comment 4' +': ' + rtrim(so.comments_4) AS Record_Notes

if that doesn't work, then I'll need to see the whole query to find the problem.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

if that doesn't work, then I'll need to see the whole query to find the problem.

Should be the best way to get a quick and good response.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I'm assuming that my answer will work because the commas will actually make 4 records instead of 1... hence that might very well be the cause of the too many columns in the select statement for the insert... but this is only as good as the guess can get.

myschif Say Hey Kid Points: 695 More actions · Answer 4

Remi -

I added the AS, though it did not change the outcome; citing the commas in your final post was the trick. I removed the commas and it worked fine.

Thanks!

"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec