July 1, 2010 at 8:48 pm
Comments posted to this topic are about the item Split string using Tally Table
July 1, 2010 at 11:01 pm
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? 😉
July 1, 2010 at 11:12 pm
I replied "None" because my mind was lost in the recursion and logic.
July 1, 2010 at 11:53 pm
Nice question..
Complex select statements ..so I ignored it and guessed the answer with the where condition.
July 1, 2010 at 11:58 pm
[p]It is very difficult to understand... and not able to get logic behind this query.. :alien:[/p]
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
July 2, 2010 at 12:57 am
This is one of several examples of practical use of Tally table
As 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 😀
July 2, 2010 at 2:00 am
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.
July 2, 2010 at 2:24 am
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 SPLIT
FROM Tally
WHERE Number <= LEN(@TextPlus) - 1
AND SUBSTRING(@TextPlus, Number, 1) = @StringDelimiter
ORDER BY Number
OPTION (MAXRECURSION 0);
July 2, 2010 at 3:13 am
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?
July 2, 2010 at 3:21 am
hugo explanation is good
Malleswarareddy
I.T.Analyst
MCITP(70-451)
July 2, 2010 at 4:28 am
jts_2003 (7/2/2010)
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?
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.
July 2, 2010 at 5:30 am
Here is the formatted (in the way I like) code:
DECLARE @Text NVARCHAR(500)
DECLARE @StringDelimiter CHAR
SELECT @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 SPLIT
FROM Tally
WHERE (NCHAR(UNICODE(SUBSTRING(@Text, Number, 1))) = @StringDelimiter
OR Number - 1 = LEN(@Text))
OPTION(MAXRECURSION 32767);
July 2, 2010 at 6:33 am
Got it right...
It took me 15 min to read and 15 second to answer;-)
Prashant Bhatt
Sr Engineer - Application Programming
July 2, 2010 at 6:42 am
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.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
July 2, 2010 at 6:49 am
jcrawf02 (7/2/2010)
Not sure why this one is tripping folks up, if you just count the delimiters, you can see it will return 13 pieces?
As I said, I thought it was a trick question and the code as presented simply wouldn't work for some reason!
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply