Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Substring column Expand / Collapse
Author
Message
Posted Wednesday, December 09, 2009 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, December 09, 2009 11:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:17 PM
Points: 36,012, Visits: 30,299
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #831999
Posted Thursday, December 10, 2009 2:56 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 702, Visits: 1,381
Will this not work?

SELECT RTRIM(COMMENTS) FROM <table_name>


Joie Andrew
"Since 1982"
Post #832600
Posted Thursday, December 10, 2009 3:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, December 10, 2009 3:16 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 702, Visits: 1,381
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
Posted Thursday, December 10, 2009 3:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, December 10, 2009 3:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:17 PM
Points: 36,012, Visits: 30,299
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #832635
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse