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 12345»»»

Split string using Tally Table Expand / Collapse
Author
Message
Posted Thursday, July 1, 2010 8:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 1:05 PM
Points: 1,913, Visits: 794
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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #946610
Posted Friday, July 2, 2010 2:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 1,605, Visits: 5,657
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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 6,048, Visits: 8,328
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);




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #946652
Posted Friday, July 2, 2010 3:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:25 AM
Points: 1,105, Visits: 4,902
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,939, Visits: 1,162
hugo explanation is good

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #946679
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse