InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 13:06:00 GMT20http://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspxDid I miss something? I didn't have the patience to go over all 54 pages of replies. Initially when the code for the cteTally was changed to be faster it used a [code="sql"]SELECT 0 UNION ALL...[/code]However in the final function it unions with 1 instead of 0. I tried both versions in the final function and they bot work actually.Mon, 18 Mar 2013 14:46:44 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspxIs there a reason that you mention subtracting the length of the final string from 8000 like so [code="sql"]ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1), 0), 8000) - s.N1[/code]and then in the final code you have[code="sql"] ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)[/code] I just wanted to make sure I'm understanding the code correctly. It looks like it runs the same either way...Wed, 13 Mar 2013 21:29:59 GMTbboyd019http://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ChrisM@Work (2/27/2013)[/b][hr][quote][b]mister.magoo (2/27/2013)[/b][hr][quote][b]ChrisM@Work (2/27/2013)[/b][hr]...About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.[/quote][b]I know what you mean, at least you are in a "safe place" here :-)[/b]It's worse when you make the silly mistakes in front of a client, insisting something is wrong or right when it patently isn't :ermm:[/quote]I know of at least one other old git who will be cackling his face off reading these posts :laugh:[/quote]Heh... nah... I've [b][i][u]BIN [/u][/i][/b]there myself. :-PWed, 27 Feb 2013 05:40:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]Paul White (2/27/2013)[/b][hr][quote][b]ChrisM@Work (2/27/2013)[/b][hr]Anyway, isn't it past your bedtime?[/quote]We can no longer be friends :laugh:[/quote]Slippers are in the post :-DWed, 27 Feb 2013 03:51:06 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ChrisM@Work (2/27/2013)[/b][hr]Anyway, isn't it past your bedtime?[/quote]We can no longer be friends :laugh:Wed, 27 Feb 2013 03:43:16 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]Paul White (2/27/2013)[/b][hr][quote][b]ChrisM@Work (2/27/2013)[/b][hr]I know of at least one other old git who will be cackling his face off reading these posts :laugh:[/quote]Oi![/quote]Heh I didn't mean you specifically Paul, though you [i]do[/i] have some history of spotting my embarrassing coding [i]faux pas[/i] :-PAnyway, isn't it past your bedtime?Wed, 27 Feb 2013 03:34:48 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ChrisM@Work (2/27/2013)[/b][hr]I know of at least one other old git who will be cackling his face off reading these posts :laugh:[/quote]Oi!Wed, 27 Feb 2013 03:30:22 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]mister.magoo (2/27/2013)[/b][hr][quote][b]ChrisM@Work (2/27/2013)[/b][hr]...About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.[/quote][b]I know what you mean, at least you are in a "safe place" here :-)[/b]It's worse when you make the silly mistakes in front of a client, insisting something is wrong or right when it patently isn't :ermm:[/quote]I know of at least one other old git who will be cackling his face off reading these posts :laugh:Wed, 27 Feb 2013 03:28:25 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ChrisM@Work (2/27/2013)[/b][hr]...About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.[/quote]I know what you mean, at least you are in a "safe place" here :-)It's worse when you make the silly mistakes in front of a client, insisting something is wrong or right when it patently isn't :ermm:Wed, 27 Feb 2013 02:30:18 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]dwain.c (2/26/2013)[/b][hr][quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]mister.magoo (2/26/2013)[/b][hr][quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]dwain.c (2/26/2013)[/b][hr][quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]peter-757102 (2/26/2013)[/b][hr]Using a binary comparisons is a good speedup for specific cases and good to be aware of [b]and certainly worth mentioning in bold[/b] with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.[/quote]The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation [i]will [/i]retain the new collation cast and could affect subsequent operations.[/quote]Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?I always use Latin1_General_BIN and now I'm not sure if that is CS or CI! :w00t::hehe:[/quote]It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:Latin1_General_BIN_:sLatin1_General_BIN_:][/quote]Surely you mean that Latin1_General_BIN [u][b]is[/b][/u] case sensitive (CS) [u][b]and[/b][/u] accent sensitive (AS) ?Binary collation will definitely find 'a' and 'A' to be different and 'á' to be different to 'a'.That is part of the reason it is quicker for CHARINDEX and equality testing - it doesn't have to compare 'a' to 'A','á','Á' etc... it is either 'a' or NOT 'a'.I do agree that it is smirk sensitive though :o) != :O)[/quote]Oops! Smirk in me eyes ;-) yes of course. "binary" is a bit of a giveaway.[/quote]Doh! (Homer Simpson moment) :-P[/quote]About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.Wed, 27 Feb 2013 01:51:19 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]mister.magoo (2/26/2013)[/b][hr][quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]dwain.c (2/26/2013)[/b][hr][quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]peter-757102 (2/26/2013)[/b][hr]Using a binary comparisons is a good speedup for specific cases and good to be aware of [b]and certainly worth mentioning in bold[/b] with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.[/quote]The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation [i]will [/i]retain the new collation cast and could affect subsequent operations.[/quote]Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?I always use Latin1_General_BIN and now I'm not sure if that is CS or CI! :w00t::hehe:[/quote]It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:Latin1_General_BIN_:sLatin1_General_BIN_:][/quote]Surely you mean that Latin1_General_BIN [u][b]is[/b][/u] case sensitive (CS) [u][b]and[/b][/u] accent sensitive (AS) ?Binary collation will definitely find 'a' and 'A' to be different and 'á' to be different to 'a'.That is part of the reason it is quicker for CHARINDEX and equality testing - it doesn't have to compare 'a' to 'A','á','Á' etc... it is either 'a' or NOT 'a'.I do agree that it is smirk sensitive though :o) != :O)[/quote]Oops! Smirk in me eyes ;-) yes of course. "binary" is a bit of a giveaway.[/quote]Doh! (Homer Simpson moment) :-PTue, 26 Feb 2013 17:12:52 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]mister.magoo (2/26/2013)[/b][hr][quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]dwain.c (2/26/2013)[/b][hr][quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]peter-757102 (2/26/2013)[/b][hr]Using a binary comparisons is a good speedup for specific cases and good to be aware of [b]and certainly worth mentioning in bold[/b] with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.[/quote]The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation [i]will [/i]retain the new collation cast and could affect subsequent operations.[/quote]Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?I always use Latin1_General_BIN and now I'm not sure if that is CS or CI! :w00t::hehe:[/quote]It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:Latin1_General_BIN_:sLatin1_General_BIN_:][/quote]Surely you mean that Latin1_General_BIN [u][b]is[/b][/u] case sensitive (CS) [u][b]and[/b][/u] accent sensitive (AS) ?Binary collation will definitely find 'a' and 'A' to be different and 'á' to be different to 'a'.That is part of the reason it is quicker for CHARINDEX and equality testing - it doesn't have to compare 'a' to 'A','á','Á' etc... it is either 'a' or NOT 'a'.I do agree that it is smirk sensitive though :o) != :O)[/quote]Oops! Smirk in me eyes ;-) yes of course. "binary" is a bit of a giveaway.Tue, 26 Feb 2013 05:15:43 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]dwain.c (2/26/2013)[/b][hr][quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]peter-757102 (2/26/2013)[/b][hr]Using a binary comparisons is a good speedup for specific cases and good to be aware of [b]and certainly worth mentioning in bold[/b] with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.[/quote]The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation [i]will [/i]retain the new collation cast and could affect subsequent operations.[/quote]Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?I always use Latin1_General_BIN and now I'm not sure if that is CS or CI! :w00t::hehe:[/quote]It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:Latin1_General_BIN_:sLatin1_General_BIN_:][/quote]Surely you mean that Latin1_General_BIN [u][b]is[/b][/u] case sensitive (CS) [u][b]and[/b][/u] accent sensitive (AS) ?Binary collation will definitely find 'a' and 'A' to be different and 'á' to be different to 'a'.That is part of the reason it is quicker for CHARINDEX and equality testing - it doesn't have to compare 'a' to 'A','á','Á' etc... it is either 'a' or NOT 'a'.I do agree that it is smirk sensitive though :o) != :O)Tue, 26 Feb 2013 05:11:56 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]dwain.c (2/26/2013)[/b][hr][quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]peter-757102 (2/26/2013)[/b][hr]Using a binary comparisons is a good speedup for specific cases and good to be aware of [b]and certainly worth mentioning in bold[/b] with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.[/quote]The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation [i]will [/i]retain the new collation cast and could affect subsequent operations.[/quote]Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?I always use Latin1_General_BIN and now I'm not sure if that is CS or CI! :w00t::hehe:[/quote]It's neither case nor accent sensitive but there's evidence here that it's smirk-sensitive:Latin1_General_BIN_:sLatin1_General_BIN_:]Tue, 26 Feb 2013 04:46:47 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ChrisM@Work (2/26/2013)[/b][hr][quote][b]peter-757102 (2/26/2013)[/b][hr]Using a binary comparisons is a good speedup for specific cases and good to be aware of [b]and certainly worth mentioning in bold[/b] with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.[/quote]The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation [i]will [/i]retain the new collation cast and could affect subsequent operations.[/quote]Does this mean though, that internally the BIN collation sequence should be either CS or CI, depending on what the default is for the database?I always use Latin1_General_BIN and now I'm not sure if that is CS or CI! :w00t::hehe:Tue, 26 Feb 2013 04:32:44 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]peter-757102 (2/26/2013)[/b][hr]Using a binary comparisons is a good speedup for specific cases and good to be aware of [b]and certainly worth mentioning in bold[/b] with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.[/quote]The collation changes to DS8K suggested by Mister Magoo are all internal to the function and are not inherited by (or retained by) the output columns. Good point though - the output of multiple nested REPLACE accelerated by applying a binary collation [i]will [/i]retain the new collation cast and could affect subsequent operations.Tue, 26 Feb 2013 03:53:10 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspxUsing a binary comparisons is a good speedup for specific cases and good to be aware of [b]and certainly worth mentioning in bold[/b] with its benefits. What it is not however is a good in a generic solution that people can copy paste and directly use without unexpected side-effects. Collations do not exist for nothing and most of the searches in a database will be case insensitve for valid reasons.Tue, 26 Feb 2013 03:48:03 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]Usman Butt (2/22/2013)[/b][hr]And to supplement my earlier post, please read posts from [url=http://www.sqlservercentral.com/Forums/FindPost1356692.aspx]here[/url] onwards. I guess both of our top experts Jeff Moden and Paul White already knows it very well? :hehe:[/quote]Ouch. Less than 6 months ago and I already forgot!Fri, 22 Feb 2013 14:02:30 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]mister.magoo (2/22/2013)[/b][hr][quote][b]Usman Butt (2/22/2013)[/b][hr][quote][b]L' Eomot Inversé (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr]What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"? Always makes me nervous when that is the case...[/quote]That feeling was bothering me too - I can't understand why I didn't spot the opportunity the first time I looked at the code, let alone why Jeff or Paul or another of the top experts who contribute so much to SQLServerCentral didn't do it long ago.Looks as if you're the only one who's awake around here, Mr M. :cool:[/quote]IIRC, Jeff Moden already knows it. But I guess the conversation we had somewhere else was something like that he likes to use the default collation. (I may be wrong. So I sincerely apologize in advance in case I said anything/everything wrong).But in my case, the binary collated splitter version is already in production for comma delimeted strings ;-)[/quote][/quote]And to supplement my earlier post, please read posts from [url=http://www.sqlservercentral.com/Forums/FindPost1356692.aspx]here[/url] onwards. I guess both of our top experts Jeff Moden and Paul White already knows it very well? :hehe:Fri, 22 Feb 2013 13:24:24 GMTUsman Butthttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]Usman Butt (2/22/2013)[/b][hr][quote][b]L' Eomot Inversé (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr]What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"? Always makes me nervous when that is the case...[/quote]That feeling was bothering me too - I can't understand why I didn't spot the opportunity the first time I looked at the code, let alone why Jeff or Paul or another of the top experts who contribute so much to SQLServerCentral didn't do it long ago.Looks as if you're the only one who's awake around here, Mr M. :cool:[/quote]IIRC, Jeff Moden already knows it. But I guess the conversation we had somewhere else was something like that he likes to use the default collation. (I may be wrong. So I sincerely apologize in advance in case I said anything/everything wrong).But in my case, the binary collated splitter version is already in production for comma delimeted strings ;-)[/quote]See, I knew someone must have done it alreadyFri, 22 Feb 2013 13:16:37 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]L' Eomot Inversé (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr]What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"? Always makes me nervous when that is the case...[/quote]That feeling was bothering me too - I can't understand why I didn't spot the opportunity the first time I looked at the code, let alone why Jeff or Paul or another of the top experts who contribute so much to SQLServerCentral didn't do it long ago.Looks as if you're the only one who's awake around here, Mr M. :cool:[/quote]Actually, the answer is embarrasingly simple. I flat out forgot. :blush:Fri, 22 Feb 2013 13:09:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]L' Eomot Inversé (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr]What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"? Always makes me nervous when that is the case...[/quote]That feeling was bothering me too - I can't understand why I didn't spot the opportunity the first time I looked at the code, let alone why Jeff or Paul or another of the top experts who contribute so much to SQLServerCentral didn't do it long ago.Looks as if you're the only one who's awake around here, Mr M. :cool:[/quote]IIRC, Jeff Moden already knows it. But I guess the conversation we had somewhere else was something like that he likes to use the default collation. (I may be wrong. So I sincerely apologize in advance in case I said anything/everything wrong).But in my case, the binary collated splitter version is already in production for comma delimeted strings ;-)Fri, 22 Feb 2013 13:08:25 GMTUsman Butthttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ErikEckhardt (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][code="sql"] WHERE SUBSTRING(@pString,t.N,1) [u]COLLATE Latin1_General_BIN[/u] = @pDelimiter [u]COLLATE Latin1_General_BIN[/u][/code][/quote]COLLATE only needs to be on one side of the expression. Either side will do. You don't have to put it on both sides.[/quote]Yes, you are right. I added it one time too many, but as it was there when I ran the tests, I thought it best to leave it. I am sure that Jeff would remove the extra one...Fri, 22 Feb 2013 12:34:01 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspxFirst off, this was a great article followed by an awesome thread… great suggestions, dialog, debates and explanations. Even though Jeff was kind enough to update the article on 5/12/2011, it seems like there has been some good suggestions since then. Unfortunately, in the 53 pages of posts, I've lost track of the "latest version". Could someone point me to the best post? Or, is the 5/12/2011 update the best.Fri, 22 Feb 2013 12:10:51 GMTONEWAREhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]mister.magoo (2/22/2013)[/b][code="sql"] WHERE SUBSTRING(@pString,t.N,1) [u]COLLATE Latin1_General_BIN[/u] = @pDelimiter [u]COLLATE Latin1_General_BIN[/u][/code][/quote]COLLATE only needs to be on one side of the expression. Either side will do. You don't have to put it on both sides.Fri, 22 Feb 2013 12:01:39 GMTErikEckhardthttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]dwain.c (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr][quote][b]ChrisM@Work (2/22/2013)[/b][hr][quote][b]L' Eomot Inversé (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr]And here is the code (with notes removed for brevity) and the changes underlined.Sorry if I have made a mistake, but I think this is valid.[/quote]Looks good to me - using a binary collation probably speeds up the comparison....[/quote]Confirmed. A process I'm currently working on which makes extensive use of the function, runs in less than half the time using MM's modification.[/quote]Nice to hear confirmation from a real example.What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"? Always makes me nervous when that is the case...[/quote]Are you saying all you did was add that COLLATE in 2 places???Magoo, you've done it again![/quote]I did use binary COLLATion only in one place and used INT datatype for the Phyical tally table quite a bit of time ago, which showed twice as much of improvement ;-). I did share it here but somehow it got lost in some other debates :-) Nice to see it is getting more attention now :cool:Fri, 22 Feb 2013 11:14:19 GMTUsman Butthttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]mister.magoo (2/22/2013)[/b][hr]What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"? Always makes me nervous when that is the case...[/quote]That feeling was bothering me too - I can't understand why I didn't spot the opportunity the first time I looked at the code, let alone why Jeff or Paul or another of the top experts who contribute so much to SQLServerCentral didn't do it long ago.Looks as if you're the only one who's awake around here, Mr M. :cool:Fri, 22 Feb 2013 11:04:59 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]dwain.c (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr][quote][b]ChrisM@Work (2/22/2013)[/b][hr][quote][b]L' Eomot Inversé (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr]And here is the code (with notes removed for brevity) and the changes underlined.Sorry if I have made a mistake, but I think this is valid.[/quote]Looks good to me - using a binary collation probably speeds up the comparison....[/quote]Confirmed. A process I'm currently working on which makes extensive use of the function, runs in less than half the time using MM's modification.[/quote]Nice to hear confirmation from a real example.What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"? Always makes me nervous when that is the case...[/quote]Are you saying all you did was add that COLLATE in 2 places???Magoo, you've done it again![/quote]Well, to be fair, I did "add that COLLATE" clause in four times, so it's not like I was slacking off or nothing...:-)And, I did test it as well and put the results in Excel, so all in all I need a lie down :doze:Fri, 22 Feb 2013 09:26:09 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]mister.magoo (2/22/2013)[/b][hr][quote][b]ChrisM@Work (2/22/2013)[/b][hr][quote][b]L' Eomot Inversé (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr]And here is the code (with notes removed for brevity) and the changes underlined.Sorry if I have made a mistake, but I think this is valid.[/quote]Looks good to me - using a binary collation probably speeds up the comparison....[/quote]Confirmed. A process I'm currently working on which makes extensive use of the function, runs in less than half the time using MM's modification.[/quote]Nice to hear confirmation from a real example.What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"? Always makes me nervous when that is the case...[/quote]Are you saying all you did was add that COLLATE in 2 places???Magoo, you've done it again!Fri, 22 Feb 2013 09:02:39 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ChrisM@Work (2/22/2013)[/b][hr][quote][b]L' Eomot Inversé (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr]And here is the code (with notes removed for brevity) and the changes underlined.Sorry if I have made a mistake, but I think this is valid.[/quote]Looks good to me - using a binary collation probably speeds up the comparison....[/quote]Confirmed. A process I'm currently working on which makes extensive use of the function, runs in less than half the time using MM's modification.[/quote]Nice to hear confirmation from a real example.What bothered me was that feeling that "someone should have thought of this ages ago, shouldn't they"? Always makes me nervous when that is the case...Fri, 22 Feb 2013 07:55:50 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]L' Eomot Inversé (2/22/2013)[/b][hr][quote][b]mister.magoo (2/22/2013)[/b][hr]And here is the code (with notes removed for brevity) and the changes underlined.Sorry if I have made a mistake, but I think this is valid.[/quote]Looks good to me - using a binary collation probably speeds up the comparison....[/quote]Confirmed. A process I'm currently working on which makes extensive use of the function, runs in less than half the time using MM's modification.Fri, 22 Feb 2013 07:53:27 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]mister.magoo (2/22/2013)[/b][hr]And here is the code (with notes removed for brevity) and the changes underlined.Sorry if I have made a mistake, but I think this is valid.[/quote]Looks good to me - using a binary collation probably speeds up the comparison.Something completely different though: the three comments --10E+1 or 10 rows--10E+2 or 100 rows--10E+4 or 10,000 rows maxare all wrong: "10E" should be "1E" in each case.Fri, 22 Feb 2013 07:48:54 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]TheSQLGuru (2/22/2013)[/b][hr]Hey, NOT COOL!! We do PUBLIC performance testing around these parts! You test it, you post it! :hehe:[/quote]Right, if I get flamed for this, you have to save me !I have crudely highlighted the two winners...the CLR split clearly still way ahead, but the new T-SQL version not too shabby[img]http://www.sqlservercentral.com/Forums/Attachment13241.aspx[/img]Attached testing results data spreadsheet for those that trust me enough to open it (it is safe !)And here is the code (with notes removed for brevity) and the changes underlined.[code="sql"]CREATE FUNCTION [dbo].[DelimitedSplit8KB]--===== Define I/O parameters (@pString VARCHAR(8000) , @pDelimiter CHAR(1))--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!RETURNS TABLE WITH SCHEMABINDING AS RETURN--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ),cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) [u]COLLATE Latin1_General_BIN[/u] = @pDelimiter [u]COLLATE Latin1_General_BIN[/u] ),cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter [u]COLLATE Latin1_General_BIN[/u],@pString [u]COLLATE Latin1_General_BIN[/u],s.N1) ,0)-s.N1,8000) FROM cteStart s )--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pString, l.N1, l.L1) FROM cteLen l;[/code]Sorry if I have made a mistake, but I think this is valid.Fri, 22 Feb 2013 06:15:47 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]mister.magoo (2/21/2013)[/b][hr]Hi Jeff, I just sent you an email with some interesting performance test results if you get a chance to have a look.:cool:[/quote]Hey, NOT COOL!! We do PUBLIC performance testing around these parts! You test it, you post it! :hehe:Fri, 22 Feb 2013 05:44:06 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspxHi Jeff, I just sent you an email with some interesting performance test results if you get a chance to have a look.:cool:Thu, 21 Feb 2013 17:41:03 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ahpitre (2/18/2013)[/b][hr]THis function works great, but, has the following limitations or things that can be improved :1) If the string to be splitted has the delimiter character at the end, then, it returns an additional item/ItemNumber row where item will be empty. For example :Searching for ; on a string that contains 'ddkfjdkdjkdfkdjl;1111;' will result in :ItemNumber Item------------------------------------------1 ddkfjdkdjkdfkdjl2 11113 So, function needs to be fixed to not return the last item if it will result in an empty item (NULL or '').2) I managed to put all my delimiters on a table, then, CROSS Join my main table to the Delimiters table. The result allows you to dynamically insert the character being searched for, and, searching for multiple characters without having to hard code them on the SQL command. An example of my 1st step prior to using the delimiter8k function :SELECT * FROM dbo.tblDelimiters CROSS JOIN dbo.qryDoc_Ref_Specs_to_Doc_Controlling_PartsWHERE (dbo.qryDoc_Ref_Specs_to_Doc_Controlling_Parts.[Reference Specs] LIKE N'%' + dbo.tblDelimiters.delimiter)I then use the above results as my data source for the CROS APPLY query with the delimited8K function. My query then looks like this :SELECT DISTINCT *FROM [ESM].[dbo].[qryDoc_Ref_Specs_to_Doc_Controlling_Parts_delimited_Ref_Specs] CROSS APPLY dbo.DelimitedSplit8K([Reference Specs], [Reference Specs delimiter])[/quote]Delimiter at the end means the same thing as a delimiter at the beginning. There's a missing element. Don't fix it in the splitter. Fix it in the code that uses the delimiter output.Mon, 18 Feb 2013 12:18:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspxTHis function works great, but, has the following limitations or things that can be improved :1) If the string to be splitted has the delimiter character at the end, then, it returns an additional item/ItemNumber row where item will be empty. For example :Searching for ; on a string that contains 'ddkfjdkdjkdfkdjl;1111;' will result in :ItemNumber Item------------------------------------------1 ddkfjdkdjkdfkdjl2 11113 So, function needs to be fixed to not return the last item if it will result in an empty item (NULL or '').2) I managed to put all my delimiters on a table, then, CROSS Join my main table to the Delimiters table. The result allows you to dynamically insert the character being searched for, and, searching for multiple characters without having to hard code them on the SQL command. An example of my 1st step prior to using the delimiter8k function :SELECT * FROM dbo.tblDelimiters CROSS JOIN dbo.qryDoc_Ref_Specs_to_Doc_Controlling_PartsWHERE (dbo.qryDoc_Ref_Specs_to_Doc_Controlling_Parts.[Reference Specs] LIKE N'%' + dbo.tblDelimiters.delimiter)I then use the above results as my data source for the CROS APPLY query with the delimited8K function. My query then looks like this :SELECT DISTINCT *FROM [ESM].[dbo].[qryDoc_Ref_Specs_to_Doc_Controlling_Parts_delimited_Ref_Specs] CROSS APPLY dbo.DelimitedSplit8K([Reference Specs], [Reference Specs delimiter])Mon, 18 Feb 2013 09:29:53 GMTahpitrehttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx[quote][b]ahpitre (2/13/2013)[/b][hr]How do you use this function? Also, how can I pass an additional parameter, so it's always inserted into the new table? I have a column named Part. I want the table with the split to include Part (which is repeated for all substrings that are splitted from main string). My final output should be something like this :Input_tablePart Specs---------------------------------123 D-dfldkk; P-4987843; D48974587456 A-dfldkk; Z-4987843Output_table (created by Split function)Part Specs---------------------------------123 D-dfldkk123 P-4987843123 D48974587456 A-dfldkk456 Z-4987843[/quote]By using cross apply. I will show you the code but please PLEASE PLEASE do NOT just blindly use this code. You need to understand what it is doing so you are able to support it. It is your phone that will be ringing at 3am not mine. :-P[code]if object_id('tempdb..#Input') is not null drop table #Input create table #Input( Part int, Specs varchar(50))insert #Input select 123, 'D-dfldkk; P-4987843; D48974587' union all select 456, 'A-dfldkk; Z-4987843'--The above is your table--To avoid the performance issues with using replace while calling the DelimitedSplit8K function--we can do this with a cte;with cte as( select Part, Replace(Specs, '; ', ';') as Specs from #Input)--Now we just need to retrieve the dataselect cte.Part, s.Item as Specs from ctecross apply dbo.DelimitedSplit8k(cte.Specs, ';') s[/code]Wed, 13 Feb 2013 12:36:29 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspxTested function with 2 delimiters, works great, although it takes some time to run (20 minutes to run on a table with 887,994 rows whose splitting resulted in 13,397,215 rows).Wed, 13 Feb 2013 12:31:10 GMTahpitrehttp://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspxHow do you use this function? Also, how can I pass an additional parameter, so it's always inserted into the new table? I have a column named Part. I want the table with the split to include Part (which is repeated for all substrings that are splitted from main string). My final output should be something like this :Input_tablePart Specs---------------------------------123 D-dfldkk; P-4987843; D48974587456 A-dfldkk; Z-4987843Output_table (created by Split function)Part Specs---------------------------------123 D-dfldkk123 P-4987843123 D48974587456 A-dfldkk456 Z-4987843Wed, 13 Feb 2013 10:58:30 GMTahpitre