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 «««56789»»

Performance Tuning: Concatenation Functions and Some Tuning Myths Expand / Collapse
Author
Message
Posted Wednesday, March 19, 2008 2:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:53 PM
Points: 266, Visits: 2,589
Matt: I've run into that very problem on more than one occasion. Sometimes I do a replace() to fix the problem. Sometimes I can change the text to avoid the encoding (?not sure if that's the right term) text.

Here's another trick. What if you want a line return between each row instead of a comma? You might be tempted to use the following to create a new row:
SELECT Char(13) + Char(10) + MyColumn ...


But if you do that, you get this garbeldyguk at the end of each line: "
" Ugh. The trick is to do the Char(10) by itself without the Char(13). Then you get line returns without the added text. This may be obvious to people, but it wasn't to me. It took some playing to figure it out. So, I thought I would share.
Post #471889
Posted Wednesday, March 19, 2008 2:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:53 PM
Points: 266, Visits: 2,589
Mike: I'd be interested to know if you ever get the TYPE option to work. I had read about it and tried it, but it never worked for me. Then again, I may have been doing something wrong.
Post #471891
Posted Wednesday, March 19, 2008 2:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
JJ B (3/19/2008)
Mike: I'd be interested to know if you ever get the TYPE option to work. I had read about it and tried it, but it never worked for me. Then again, I may have been doing something wrong.


I just tried it and it didn't change the outcome I'll play around with it later and see if I can get it working. It may end up being a case where REPLACE is required on the 5 entities:
< 
>
"
'
&

Post #471907
Posted Thursday, March 20, 2008 9:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:53 PM
Points: 266, Visits: 2,589
Mike: Thanks for looking into it. Glad to know I didn't miss something terribly obvious.

re: "replace required on certain characters. "
I would add: & # x 0 D ;
as characters that may need replacing.
(without the spaces. I can't figure out how to get this to display as-is. What did you do to get the above encodings to appear as-is?)
Post #472343
Posted Thursday, March 20, 2008 9:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:24 PM
Points: 7,139, Visits: 15,191
JJ B (3/20/2008)
Mike: Thanks for looking into it. Glad to know I didn't miss something terribly obvious.

re: "replace required on certain characters. "
I would add: & # x 0 D ;
as characters that may need replacing.
(without the spaces. I can't figure out how to get this to display as-is. What did you do to get the above encodings to appear as-is?)

I usually "con" it by "bolding" one of the characters in the string. by having the tag in the middle of the string - HTML doesn't "kick in " and encode it.

As in - (x is bolded)




----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #472348
Posted Thursday, March 20, 2008 9:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:53 PM
Points: 266, Visits: 2,589
Matt: Great tip! I used it on my earlier post and it worked. Thanks.
Post #472352
Posted Thursday, March 20, 2008 10:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
That's what I basically did (after about 10 edit attempts). I italicized everything after the ampersand. It appears that when the parser for this editor sees an ampersand it wipes out all alphabetic characters up to the semicolon. So the trick of using > for instance, wipes out the gt; but converts the & to an ampersand. Seems like it might be a bug in the editor control. Apparently when it encounters a nonalphabetic character like [ it acts as a break between the ampersand and the alphabetic characters.
Post #472411
Posted Thursday, March 20, 2008 10:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:53 PM
Points: 266, Visits: 2,589
Thanks for the additional reply. I wish there was an easy escape character or set of characters to say "hey, please print this text as-shown". Sort of like doubling a single quote in a string to let SQL Server know that it really is just a single quote.
Post #472417
Posted Thursday, August 21, 2008 9:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
Jeff
Its brilliant. But how on earth can anyone be expected to figure out the STUFF(... WHERE ... FOR XML PATH('') Syntax - especially when there is no XML involved!
I'll be using this, but I'm a bit wary as I don't know exactly how it works

Post #556687
Posted Saturday, August 23, 2008 10:51 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
Tom Brown (8/21/2008)
Jeff
Its brilliant. But how on earth can anyone be expected to figure out the STUFF(... WHERE ... FOR XML PATH('') Syntax - especially when there is no XML involved!
I'll be using this, but I'm a bit wary as I don't know exactly how it works



Thanks... I wish I could take credit for it, but I can't.

I haven't done an indepth analysis of the method, but I've read that it tries to make an XML path out of the individual items and, because the "root" is supressed, it generates not tags. The "STUFF" is just to kill the leading comma.


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

(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 #557780
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse