Substring column

Question

Substring column

jim.rasmussen

SSC Eights!

Points: 999
More actions
December 9, 2009 at 11:32 am

#140601

I have a table with a column for comments. VARCHAR(1000). When executing a query in QA and results to text option I get this field 1000 characters wide. When this is used, SUBSTRING(COMMENTS, 1, 4) al is good but, when I use this SUBSTRING(COMMENTS, 1, LEN(COMMENTS)) and the length is 4 I get the full length of the field. is there any type of formating that can be done to fix this? My ultimate goal is to have this query be sent my xp_sendmail.
Thanks
Jim

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

SUBSTRING(COMMENTS, 1, LEN(COMMENTS)) will always return the full length of the field. Look at it... you need to do something else.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

Will this not work?

SELECT RTRIM(COMMENTS) FROM <table_name>

Joie Andrew
"Since 1982"

jim.rasmussen SSC Eights! Points: 999 More actions · Answer 3

An update. I am using XP_SENDMAIL with @QUERY. The results for @QUERY can be sent as an attachment or in the message of the email. In either case the results are formated with the columns length set to the field description ie VARCHAR(1000). So a better question is can the results from @QUERY be formated with XP_SENDMAIL?

Thanks

Jim

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 4

Have you tested it? If you run the query statement that is being assigned to @query in management studio and have the output written to a file that would probably give you a good idea of what it would look like as an attachment when being sent with xp_sendmail. What happens when you try using xp_sendmail to send the e-mail to yourself?

Joie Andrew
"Since 1982"

jim.rasmussen SSC Eights! Points: 999 More actions · Answer 5

The RTRIM(COMMENTS) does not give me the correct results. The 'header line' that is generated by 'results to file' option is 1000 characters. But I have found a solution with using Crystal Reports and our main application.

Thanks for the asist.

Jim

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

Ah... now I see what you're trying to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)