Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Split string using Tally Table Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 1, 2010 8:48 PM
 SSC Veteran Group: General Forum Members Last Login: Thursday, May 9, 2013 9:59 AM Points: 236, Visits: 149
 Comments posted to this topic are about the item Split string using Tally Table
Post #946569
 Posted Thursday, July 1, 2010 11:01 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, June 30, 2015 9:47 AM Points: 2,160, Visits: 2,204
 Nice brain teaser of a question. Wow!Though aren't the "None" and "0" choices the same? (I assume that 0 rows is no rows.)Did it really need to be that complicated?
Post #946584
 Posted Thursday, July 1, 2010 11:12 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, August 18, 2014 4:04 AM Points: 176, Visits: 567
 I replied "None" because my mind was lost in the recursion and logic.
Post #946585
 Posted Thursday, July 1, 2010 11:53 PM
 SSCrazy Group: General Forum Members Last Login: Friday, March 4, 2016 7:14 AM Points: 2,130, Visits: 834
 Nice question..Complex select statements ..so I ignored it and guessed the answer with the where condition. Regards,Rals.
Post #946593
 Posted Thursday, July 1, 2010 11:58 PM
 SSC Veteran Group: General Forum Members Last Login: Monday, July 22, 2013 1:05 AM Points: 248, Visits: 373
 It is very difficult to understand... and not able to get logic behind this query.. KSB-----------------------------Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
Post #946595
 Posted Friday, July 2, 2010 12:57 AM
 Hall of Fame Group: General Forum Members Last Login: Saturday, June 6, 2015 9:44 PM Points: 3,450, Visits: 4,408
 This is one of several examples of practical use of Tally tableAs for me, this is one of several the thousands examples of totally unformatted and obscure code. An example that would be convenient for the 'how-to-not-format-your-code' guide
Post #946610
 Posted Friday, July 2, 2010 2:00 AM
 SSCommitted Group: General Forum Members Last Login: Friday, October 14, 2016 2:57 AM Points: 1,833, Visits: 6,210
 I 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.
Post #946635
 Posted Friday, July 2, 2010 2:24 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 9:30 AM Points: 7,765, Visits: 11,375
 I 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 Erland Sommarskog's website.`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);` Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #946652
 Posted Friday, July 2, 2010 3:13 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, March 3, 2016 8:04 AM Points: 1,140, Visits: 5,009
 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?
Post #946675
 Posted Friday, July 2, 2010 3:21 AM
 SSCommitted Group: General Forum Members Last Login: Wednesday, July 29, 2015 11:50 PM Points: 1,967, Visits: 1,189
 hugo explanation is good MalleswarareddyI.T.AnalystMCITP(70-451)
Post #946679

 Permissions