Substring column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » Substring column

Substring column

Rate Topic

Display Mode

Topic Options

Author

Message

jim.rasmussen

Posted Wednesday, December 09, 2009 11:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 14, 2012 6:45 AM
Points: 49, Visits: 122

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

Post #831674

Jeff Moden

Posted Wednesday, December 09, 2009 11:12 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 33,110, Visits: 27,035

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #831999

Joie Andrew

Posted Thursday, December 10, 2009 2:56 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:01 AM
Points: 576, Visits: 1,103

Will this not work?

SELECT RTRIM(COMMENTS) FROM <table_name>

Joie Andrew
"Since 1982"

Post #832600

jim.rasmussen

Posted Thursday, December 10, 2009 3:03 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 14, 2012 6:45 AM
Points: 49, Visits: 122

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

Post #832605

Joie Andrew

Posted Thursday, December 10, 2009 3:16 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:01 AM
Points: 576, Visits: 1,103

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"

Post #832610

jim.rasmussen

Posted Thursday, December 10, 2009 3:50 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 14, 2012 6:45 AM
Points: 49, Visits: 122

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

Post #832630

Jeff Moden

Posted Thursday, December 10, 2009 3:56 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 33,110, Visits: 27,035

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

--Jeff Moden

Post #832635

« Prev Topic | Next Topic »

Permissions