InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 16:24:20 GMT20http://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxI selected None as i got lost in the logic. also i thought 0 & none would be same isn't it?Sun, 07 Nov 2010 16:43:40 GMTDhruvesh Shahhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]Hugo Kornelis (7/13/2010)[/b][hr]Both the text before the first delimiter and the text after the last delimiter will be a row, as each bit of text between two delimiters. That results in #rows = #delimiters + 1.You can see this in the WHERE clause:WHERE (...) = @StringDelimiter -- Twelve rows, for the twelve delimiters.OR Number - 1 = LEN(@Text) -- One row, for the end of the text.If the last character is a delimiter (that is, if the text had ended in a dot instead of a question mark), the number of rows would be equal to the number of delimiters, with the "last" row matching both of the OR'ed conditions.[/quote]Hugo,thank you very much for your detailed explanation.In my mind, a sentence always ends with a dot; hence I was only looking at the left side of the delimiter. Just realized after my initial post that the last piece of text ended with a question mark; so the right hand side needs to be considered as well.Thanks again,MichaelTue, 13 Jul 2010 07:32:31 GMTmichael.kaufmannhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]michael.kaufmann (7/13/2010)[/b][hr][quote][b]jcrawf02 (7/2/2010)[/b][hr]Not sure why this one is tripping folks up, if you just count the delimiters, you can see it will return 13 pieces?...anyway...good question, I'm sure it's introduced some folks to this idea that haven't seen it before.[/quote]That's the point I don't get--when counting the delimiters of the @text variable, I'll only come up with 12.What am I missing here?Thanks[/quote]Both the text before the first delimiter and the text after the last delimiter will be a row, as each bit of text between two delimiters. That results in #rows = #delimiters + 1.You can see this in the WHERE clause:WHERE (...) = @StringDelimiter -- Twelve rows, for the twelve delimiters.OR Number - 1 = LEN(@Text) -- One row, for the end of the text.If the last character is a delimiter (that is, if the text had ended in a dot instead of a question mark), the number of rows would be equal to the number of delimiters, with the "last" row matching both of the OR'ed conditions.Tue, 13 Jul 2010 06:58:55 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]michael.kaufmann (7/13/2010)[/b][hr][quote][b]jcrawf02 (7/2/2010)[/b][hr]Not sure why this one is tripping folks up, if you just count the delimiters, you can see it will return 13 pieces?...anyway...good question, I'm sure it's introduced some folks to this idea that haven't seen it before.[/quote]That's the point I don't get--when counting the delimiters of the @text variable, I'll only come up with 12.What am I missing here?Thanks[/quote]You have to add 1: there's a piece before each delimiter and another piece after the last delimiter.Tue, 13 Jul 2010 06:57:19 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]michael.kaufmann (7/13/2010)[/b][hr][quote][b]jcrawf02 (7/2/2010)[/b][hr]Not sure why this one is tripping folks up, if you just count the delimiters, you can see it will return 13 pieces?...anyway...good question, I'm sure it's introduced some folks to this idea that haven't seen it before.[/quote]That's the point I don't get--when counting the delimiters of the @text variable, I'll only come up with 12.What am I missing here?[/quote]Just found out--was only looking at the left of the delimiter... ;-)Tue, 13 Jul 2010 06:56:48 GMTmichael.kaufmannhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]jcrawf02 (7/2/2010)[/b][hr]Not sure why this one is tripping folks up, if you just count the delimiters, you can see it will return 13 pieces?...anyway...good question, I'm sure it's introduced some folks to this idea that haven't seen it before.[/quote]That's the point I don't get--when counting the delimiters of the @text variable, I'll only come up with 12.What am I missing here?ThanksTue, 13 Jul 2010 06:47:05 GMTmichael.kaufmannhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxWell, I got it wrong - after looking briefly at the code and deciding there was now way I was going to try to make sense of such an ill-formated mess, feeling flabbergasted at the misuse of a recursive CTE to generate a tally table, and concluding that the where clause indicated that all I had to do was count the dots and add 1 unless it was a trck question and the code was going to return an error instead of any rows (and there was no way I was going to syntax-check anything with that layout, so assume no error) I counted the dots wrong (by leaving out the firstline - I had scrolled it off th top of my window and forgot about it). I hate code with unneeded complexity, especially when it's layed out so as to obscure its structure. I hate code that burns CPU cycles as if they were going out of fashion. It's a good question in that it does demonstrate that the select list is irrelevant when counting rows. It's a bad question in that it demonstrates a way that code should never be written.Fri, 09 Jul 2010 07:13:24 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxthanks for the questionWed, 07 Jul 2010 14:57:10 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxJeff,Thank you very much for your suggestions. You make a very good point about the iTVF, it is definitely more useful than the proc. In my defense about the tally, I can say that my decision about a good-enough-for-qotd-scope tally script is based on the following assumption:Someone playing with the script either:"Got a job" :-) and thus has the development copy of the prod database with the sufficient number of records in sys.objects"Don't got a job" :w00t: and thus has AdventureWorks database with the sufficient number of records in sys.objects (about 1800 records, 3.24 mln if cross joined).iTVF works very well with cross apply, with which I cannot play at work as we are still at compat 80 2005, so I have to constantly restate my cross apply statements as joins (when I can) when moving the test scripts between the databases at work and AdventureWorks at home.OlegWed, 07 Jul 2010 10:19:54 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]Oleg Netchaev (7/6/2010)[/b][hr][quote][b]Jeff Moden (7/2/2010)[/b][hr]BWAA-HAA!!!! First, this is NOT a good example of Tally Table code. It doesn't use a Tally Table and it doesn't use anything that could be called efficient. Anyone who uses a recursive CTE to generate Tally numbers just doesn't know what hidden RBAR is. :-PI agree with what someone else has stated... this code should be used as an example of how NOT to write code and how NOT to accomplish a split. Same goes for any example in this thread that uses a recursive CTE to do the split. ;-)I hope no one actually copies the code to use as a split function but I will say it was very interesting in how many "extra" things were added to the code in an attempt at obfuscation. Heh... maybe it should be titled "job security methods". :hehe:[/quote]The urge to submit a post was burning me for a couple of days, but I decided to wait, because I was afraid that I will not be able to find the way to type what I think about the script in QotD without sounding rude, so I am glad that Jeff, Hugo and vk-kirov stood up to it. I agree that this is one of the finest examples how [b][i]NOT TO DO[/i][/b] something. There are some valid reasons to use recursive CTE, for example to write BOM queries in 2005 (HierarchyID is not yet available), but these reasons do not extend to creating a tally table, specifically with @text declared as varchar(500) but the recursion level restricted to 32767 (???).[code="sql"]with tally (number) as( select top (len(@text)) row_number() over (order by [object_id]) number from sys.objects)[/code]should do the trick right?Using xml to split delimited values is relatively cheap and not difficult to understand (Does not include the job security though :hehe:). Here is one example based on simply replacing the delimiter with closing and opening the node of arbitrary name and appending opening node to the head and closing to the tail:[code="sql"]use AdventureWorks;godeclare @delimiter char(1);declare @text nvarchar(500);declare @xml xml;-- set variable valuesselect @delimiter = '.', @text = 'This t-sql will split these sentences into rows.' + 'How many rows will be returned?.' + 'M.a.y.b.e..n.o.n.e.?', @xml = '<r>' + replace(@text, @delimiter, '</r><r>') + '</r>';-- here is the "split" in all its gloryselect item.value('text()[1]', 'varchar(100)') single_item from @xml.nodes('//r') R(item);[/code]The above happily returns desired output:[code="sql"]single_item-----------------------------------------------This t-sql will split these sentences into rowsHow many rows will be returned?MaybeNULLnone?[/code]Oleg[/quote]Hi Oleg,There are, indeed, a number of ways to create a Tally table including the one you used here and a similar one on another post of yours. But you do have to be careful. sys.Objects can contain very few objects. On a brand new database in 2005, it will only contain about 47 objects and even squaring that number will only return 2209 rows. Instead, my recommendation is to refer to Master.sys.All_Columns which will have at least 4000 rows in it on a full installation. So far as splits go, using one form of Tally Table or another to split smaller items (like INTs) is usually faster than trying to split such items with XML (I'm working on the tests to show you that on the other thread). Splitting larger items such as sentences is generally done faster (depending on the size) with the XML split. In either case, it's generally preferable to use an iTVF (inline Table Valued Function) over a stored procedure to add the utility of being able to pass a whole column to be split and not just a single variable.Wed, 07 Jul 2010 00:36:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]Jeff Moden (7/2/2010)[/b][hr]BWAA-HAA!!!! First, this is NOT a good example of Tally Table code. It doesn't use a Tally Table and it doesn't use anything that could be called efficient. Anyone who uses a recursive CTE to generate Tally numbers just doesn't know what hidden RBAR is. :-PI agree with what someone else has stated... this code should be used as an example of how NOT to write code and how NOT to accomplish a split. Same goes for any example in this thread that uses a recursive CTE to do the split. ;-)I hope no one actually copies the code to use as a split function but I will say it was very interesting in how many "extra" things were added to the code in an attempt at obfuscation. Heh... maybe it should be titled "job security methods". :hehe:[/quote]The urge to submit a post was burning me for a couple of days, but I decided to wait, because I was afraid that I will not be able to find the way to type what I think about the script in QotD without sounding rude, so I am glad that Jeff, Hugo and vk-kirov stood up to it. I agree that this is one of the finest examples how [b][i]NOT TO DO[/i][/b] something. There are some valid reasons to use recursive CTE, for example to write BOM queries in 2005 (HierarchyID is not yet available), but these reasons do not extend to creating a tally table, specifically with @text declared as varchar(500) but the recursion level restricted to 32767 (???).[code="sql"]with tally (number) as( select top (len(@text)) row_number() over (order by [object_id]) number from sys.objects)[/code]should do the trick right?Using xml to split delimited values is relatively cheap and not difficult to understand (Does not include the job security though :hehe:). Here is one example based on simply replacing the delimiter with closing and opening the node of arbitrary name and appending opening node to the head and closing to the tail:[code="sql"]use AdventureWorks;godeclare @delimiter char(1);declare @text nvarchar(500);declare @xml xml;-- set variable valuesselect @delimiter = '.', @text = 'This t-sql will split these sentences into rows.' + 'How many rows will be returned?.' + 'M.a.y.b.e..n.o.n.e.?', @xml = '<r>' + replace(@text, @delimiter, '</r><r>') + '</r>';-- here is the "split" in all its gloryselect item.value('text()[1]', 'varchar(100)') single_item from @xml.nodes('//r') R(item);[/code]The above happily returns desired output:[code="sql"]single_item-----------------------------------------------This t-sql will split these sentences into rowsHow many rows will be returned?MaybeNULLnone?[/code]OlegTue, 06 Jul 2010 13:16:59 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxLike a few others, I got it right a bit by chance by looking at the number of dots.Frankly, do we need such a complicated statement to learn something?I don't think so...By the way, I did not learn anything with that question!Tue, 06 Jul 2010 01:47:59 GMTEric Mamethttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxLike someone else who replied, I shortcircuited the whole thing & counted the delimiters (well, more specifically the spaces between the delimiters), assuming it was an obfuscation question not a "Haha! Tricked you!" question.Took one skim of the code under the Tally CTE and my brain went "bleh". Seen much cleaner string-splitters.An exercise in picking through nested functions & conditional statements if nothing else.Mon, 05 Jul 2010 15:29:16 GMTThe CosmicTricksterhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]jts_2003 (7/2/2010)[/b][hr]I think this is an example of why people don't like to use OVER, since it's so hard to work out what might/will be returned!I'd like to see a simpler question or articles on how OVER works - any takers?[/quote]It's not that hard at all, [i]once[/i] you understand it. Did you see [url=http://www.sqlservercentral.com/articles/T-SQL/69717/][u]this article[/u][/url] that was just recently published in April?Mon, 05 Jul 2010 08:28:36 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxI have to admit ... I was looking at this and saying "What the heck???"For all those interested, the latest version of the "DelimitedSplit8k" function can be found [url=http://www.sqlservercentral.com/Forums/FindPost944589.aspx][u]here[/u][/url].Mon, 05 Jul 2010 07:54:47 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]ricky70rana (7/2/2010)[/b][hr]Can someone explain me how the script given below is working ?Especially the CASE Statement[/quote]The CASE [i]expression[/i] (sorry about that, couldn't resist) -or rather the nested CASE expressions- are, as already mentioned, overly complicated.Basically, it makes use of LEFT(@Text, Number) to get the first [i]Number[/i] characters of the string, then uses CASE to decide if there is a @StringDelimiter somewhere in that last part - if there is, it takes the rightmost bit until the last @SDtringDelimiter, otherwise it takes the whole part.The nested CASE are required to prevent out-of-bound errors in the SUBSTRING functions.Sat, 03 Jul 2010 04:13:32 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxVery Critical :-DSat, 03 Jul 2010 03:07:36 GMTwise_vaishuhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]ricky70rana (7/2/2010)[/b][hr]Actually you are right that i want to understand how that code is working.Only was able to understand Common Table Expression out of this script. Actually I want to understand how the CASE Statement is working. Do you have an idea about that?I already gone through with your script and that is less cumbersome than this one and easy to understand. Your post is good help to understand tally table and delimiters.[/quote]Without copying the code, reformating it to a more readable style for myself, then working through it, no I really don't. It is overly obfusicated when there are much easier ways of accomplishing the necessary task of a delimited split.If you really want to understand the code, take the time to reformat the code so that you can understand the flow, then work through it like a computer would.Fri, 02 Jul 2010 22:02:38 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxActually you are right that i want to understand how that code is working.Only was able to understand Common Table Expression out of this script. Actually I want to understand how the CASE Statement is working. Do you have an idea about that?I already gone through with your script and that is less cumbersome than this one and easy to understand. Your post is good help to understand tally table and delimiters.Fri, 02 Jul 2010 21:21:02 GMTricky70ranahttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]ricky70rana (7/2/2010)[/b][hr]Can someone explain me how the script given below is working ?Especially the CASE StatementDECLARE @Text NVARCHAR(2000) DECLARE @StringDelimiter CHAR(1) SELECT @Text = 'This T-sql will split senteneces into rows.'+ 'How many rows will be returned?.'+ 'M.a.y.b.e..n.n.o.e.?', @StringDelimiter = '.'; With Tally (Number) AS ( SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number UNION ALL SELECT Number + 1 AS Number FROM Tally WHERE Number <= LEN(@Text) )SELECT CASE WHEN RIGHT(LEFT(@Text,Number), CASE WHEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) > 0 THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) - 1 ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = '' AND CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0 THEN LEFT(@Text,Number -1) ELSE RIGHT(LEFT(@Text,Number - 1), CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0 THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1 ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END) END AS SPLIT FROM TALLY WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter OR Number - 1 = LEN(@Text)) OPTION (MAXRECURSION 32767)[/quote]I realize you would like to understand what this code is doing, but if you are looking for a delimited split function, I think you will find the code I posted previously a much simplier routine to understand. Please note, however, that there are other routines out there tat may perform better as well. I know mine starts having some performance issues when you start working with character strings that are > 8000 bytes.Fri, 02 Jul 2010 21:12:54 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxCan someone explain me how the script given below is working ?Especially the CASE StatementDECLARE @Text NVARCHAR(2000) DECLARE @StringDelimiter CHAR(1) SELECT @Text = 'This T-sql will split senteneces into rows.'+ 'How many rows will be returned?.'+ 'M.a.y.b.e..n.n.o.e.?', @StringDelimiter = '.'; With Tally (Number) AS ( SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number UNION ALL SELECT Number + 1 AS Number FROM Tally WHERE Number <= LEN(@Text) )SELECT CASE WHEN RIGHT(LEFT(@Text,Number), CASE WHEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) > 0 THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) - 1 ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = '' AND CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0 THEN LEFT(@Text,Number -1) ELSE RIGHT(LEFT(@Text,Number - 1), CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0 THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1 ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END) END AS SPLIT FROM TALLY WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter OR Number - 1 = LEN(@Text)) OPTION (MAXRECURSION 32767)Fri, 02 Jul 2010 18:40:44 GMTricky70ranahttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxHere is what may be considered a better example of using a Tally table. It is dynamically created with the function. And FYI, it is formatted the way I like to format my code. I've heard some complain that it makes my code look more complex.[code="sql"]USE [SandBox]GO/****** Object: UserDefinedFunction [dbo].[DelimitedSplit] Script Date: 07/02/2010 17:28:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE function [dbo].[DelimitedSplit] ( @pString varchar(max), @pDelimiter char(1))returns tableasreturnwitha1 as (select 1 as N 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),a2 as (select 1 as N from a1 as a cross join a1 as b),a3 as (select 1 as N from a2 as a cross join a2 as b),--a4 as (select-- 1 as N-- from-- a3 as a-- cross join a2 as b),Tally as (select top (len(@pString)) row_number() over (order by a.N) as N from a3 as a cross join a2 as b),ItemSplit( ItemOrder, Item) as (SELECT N, SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)FROM TallyWHERE N < LEN(@pDelimiter + @pString + @pDelimiter) AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter)select row_number() over (order by ItemOrder) as ItemID, Itemfrom ItemSplitGOdeclare @Text varchar(max), @StringDelimiter char(1);SET @Text = 'This T-SQL will split these sentences into rows.' + 'How many rows will be returned?.' + 'M.a.y.b.e..n.o.n.e.?'; SET @StringDelimiter = '.'; select * from dbo.DelimitedSplit (@Text, @StringDelimiter);[/code]Fri, 02 Jul 2010 17:32:17 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxBWAA-HAA!!!! First, this is NOT a good example of Tally Table code. It doesn't use a Tally Table and it doesn't use anything that could be called efficient. Anyone who uses a recursive CTE to generate Tally numbers just doesn't know what hidden RBAR is. :-PI agree with what someone else has stated... this code should be used as an example of how NOT to write code and how NOT to accomplish a split. Same goes for any example in this thread that uses a recursive CTE to do the split. ;-)I hope no one actually copies the code to use as a split function but I will say it was very interesting in how many "extra" things were added to the code in an attempt at obfuscation. Heh... maybe it should be titled "job security methods". :hehe:Fri, 02 Jul 2010 11:23:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxThanks Hogo for explanation.I was completely lost in question and does not even know what to answer. A brain teaser with small query is always good but with this big query that to in a image file is a brain torture.Well, I got right but just by fluke. No efforts on trying to find, why I was right.;-)Fri, 02 Jul 2010 08:57:12 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxFor a SQL Server 2000 (and prior) dinosaur like myself, the whole concept of defining a Common Table Expresssion (CTE), never mind a recursive one, was new to me. If anyone else is in the same boat, I found the following article very helpful:http://www.4guysfromrolla.com/webtech/071906-1.shtmlFri, 02 Jul 2010 08:40:40 GMTRose Budhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxHello all and thanks for the time spend on answering my posted question.Originally i came across with a similar t-sql already posted by Hugo. That's definitely the simplest and quickest way of doing split string using tally. Thanks Hugo.Nevertheless i wanted to make sure i could get the same result by using some string functions.Sorry about the messy code... I should have submitted correctly formatted. :blush: Consider it as an extra level of difficulty. ;-)Anyway, as almost of you got it, the catch was to look to where clause...My major concern was to post an example of using tally. Back a few months ago i read some articles discussing tally and how to replace cursors and while loops with it and become a huge fan since then. :-D I know it isn't the best example, but got you guys thinking on it. :-PBest Regards,PMFri, 02 Jul 2010 08:18:16 GMTpnmmhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]jcrawf02 (7/2/2010)[/b][hr]Hugo, thanks again for the excellent re-write and explanation.Not sure why this one is tripping folks up, if you just count the delimiters, you can see it will return 13 pieces?...anyway...good question, I'm sure it's introduced some folks to this idea that haven't seen it before.[/quote]I assumed the extra crazy in string handling was doing something to consume the double/adjacent delimiter, else this was "simply" a string split using Tally. The question was so easy I assumed it was a trick. :(Fri, 02 Jul 2010 07:50:31 GMTMike Doughertyhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]jcrawf02 (7/2/2010)[/b]Not sure why this one is tripping folks up, if you just count the delimiters, you can see it will return 13 pieces?[/quote]As I said, I thought it was a trick question and the code as presented simply wouldn't work for some reason!Fri, 02 Jul 2010 06:49:47 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxHugo, thanks again for the excellent re-write and explanation.Not sure why this one is tripping folks up, if you just count the delimiters, you can see it will return 13 pieces?...anyway...good question, I'm sure it's introduced some folks to this idea that haven't seen it before.Fri, 02 Jul 2010 06:42:15 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxGot it right...It took me 15 min to read and 15 second to answer;-)Fri, 02 Jul 2010 06:33:02 GMTprashant.bhatthttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxHere is the formatted (in the way I like) code:[code="sql"]DECLARE @Text NVARCHAR(500)DECLARE @StringDelimiter CHARSELECT @Text = 'This t-sql will split these sentences into rows.' +'How many rows will be returned?.' +'M.a.y.b.e..n.o.n.e.?',@StringDelimiter = '.';WITH Tally(Number) AS( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number UNION ALL SELECT Number + 1 AS Number FROM Tally WHERE Number <= LEN(@Text))SELECT CASE WHEN RIGHT ( LEFT(@Text, Number), CASE WHEN CHARINDEX(@StringDelimiter, REVERSE(LEFT(@Text, Number - 1)), 0) > 0 THEN CHARINDEX(@StringDelimiter, REVERSE(LEFT(@Text, Number - 1)), 0) - 1 ELSE CHARINDEX(@StringDelimiter, REVERSE(LEFT(@Text, Number - 1)), 0) END ) = '' AND CHARINDEX(@StringDelimiter, REVERSE(LEFT(@Text, Number - 1))) = 0 THEN LEFT (@Text, Number - 1) ELSE RIGHT ( LEFT(@Text, Number - 1), CASE WHEN CHARINDEX(@StringDelimiter, REVERSE(LEFT(@Text, Number - 1)), 0) > 0 THEN CHARINDEX(@StringDelimiter, REVERSE(LEFT(@Text, Number - 1)), 0) - 1 ELSE CHARINDEX(@STringDelimiter, REVERSE(LEFT(@Text, Number - 1)), 0) END ) END AS SPLITFROM TallyWHERE (NCHAR(UNICODE(SUBSTRING(@Text, Number, 1))) = @StringDelimiter OR Number - 1 = LEN(@Text))OPTION(MAXRECURSION 32767);[/code]Fri, 02 Jul 2010 05:30:17 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote][b]jts_2003 (7/2/2010)[/b][hr]I think this is an example of why people don't like to use OVER, since it's so hard to work out what might/will be returned!I'd like to see a simpler question or articles on how OVER works - any takers?[/quote]Don't worry too much about the use of OVER in this question.SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), when used without FROM clause, is nothing but a contrived and needlessly complex synonym for SELECT 1.The pain in this example is the hideously complex string handling functions in the SELECT clause. I didn't even TRY to work it out. Maybe if the author posts the code in a copy/pasteable format, I might be tempted to reformat until I see how the parentheses align, and then work out the details - but even then, I doubt if it'll be worth my time.Fri, 02 Jul 2010 04:28:38 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxhugo explanation is goodFri, 02 Jul 2010 03:21:58 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxI think this is an example of why people don't like to use OVER, since it's so hard to work out what might/will be returned!I'd like to see a simpler question or articles on how OVER works - any takers?Fri, 02 Jul 2010 03:13:04 GMTjts_2003http://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxI got it right, by focusing on the WHERE clause (that alone dictates the number of rows), and hoping that there was no parentheses mismatch or similar error buried in the code.The code seems overly complicated to me. While I agree that it is "one of several examples of practical use of Tally table.", I would much rather see a GOOD example of practical use of a Tally table.Here is a much simpler and easier to understand way to get the same results. Note that I adapted this code from a snippet taken from [url=http://www.sommarskog.se/arrays-in-sql-2000.html]Erland Sommarskog's website[/url].[code="sql"]DECLARE @Text NVARCHAR(500);DECLARE @StringDelimiter CHAR(1);SET @Text = 'This T-SQL will split these sentences into rows.' +'How many rows will be returned?.' +'M.a.y.b.e..n.o.n.e.?';SET @StringDelimiter = '.';-- Add delimiter before and after text;-- This removes the need for special code to handle start and end of string.DECLARE @TextPlus NVARCHAR(502)SET @TextPlus = @StringDelimiter + @Text + @StringDelimiter;WITH Tally(Number) AS (SELECT 1 AS Number UNION ALL SELECT Number + 1 AS Number FROM Tally WHERE Number < LEN(@TextPlus))SELECT SUBSTRING(@TextPlus, Number + 1, CHARINDEX(@StringDelimiter, @TextPlus, Number + 1) - Number - 1) AS SPLITFROM TallyWHERE Number <= LEN(@TextPlus) - 1AND SUBSTRING(@TextPlus, Number, 1) = @StringDelimiterORDER BY NumberOPTION (MAXRECURSION 0);[/code]Fri, 02 Jul 2010 02:24:49 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxI selected None because I didn't understand the code and was pretty sure it would return a syntax error...I'm hoping the author was deliberately going for obfuscation here, because if he normally writes code like this then he could be in trouble! :-)Oh, and I wouldn't say that "none" and "0" are the same--to my mind, "none" means the code threw an error while "0" means it works but just doesn't return any rows.Fri, 02 Jul 2010 02:00:32 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[quote]This is one of several examples of practical use of Tally table[/quote]As for me, this is one of [strike]several[/strike] the thousands examples of totally unformatted and obscure code. An example that would be convenient for the 'how-to-not-format-your-code' guide :-DFri, 02 Jul 2010 00:57:15 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspx[p]It is very difficult to understand... and not able to get logic behind this query.. :alien:[/p]Thu, 01 Jul 2010 23:58:07 GMTKari Sureshhttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxNice question..Complex select statements ..so I ignored it and guessed the answer with the where condition.Thu, 01 Jul 2010 23:53:28 GMTralshttp://www.sqlservercentral.com/Forums/Topic946569-2729-1.aspxI replied "None" because my mind was lost in the recursion and logic.Thu, 01 Jul 2010 23:12:17 GMTOpen Minded