Split string using Tally Table

  • pnmm

    Ten Centuries

    Points: 1280

    Comments posted to this topic are about the item Split string using Tally Table

  • UMG Developer

    SSChampion

    Points: 13482

    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? 😉

  • Open Minded

    SSCommitted

    Points: 1842

    I replied "None" because my mind was lost in the recursion and logic.

  • ralm

    Hall of Fame

    Points: 3156

    Nice question..

    Complex select statements ..so I ignored it and guessed the answer with the where condition.

    [font="Verdana"]Regards,
    Rals
    [/font].
  • Kari Suresh

    Hall of Fame

    Points: 3712

    [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

  • vk-kirov

    SSCertifiable

    Points: 7686

    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 😀

  • paul.knibbs

    SSCoach

    Points: 15270

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • jts2013

    Hall of Fame

    Points: 3226

    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?

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    hugo explanation is good

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • vk-kirov

    SSCertifiable

    Points: 7686

    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);

  • prashant.bhatt

    SSC-Addicted

    Points: 476

    Got it right...

    It took me 15 min to read and 15 second to answer;-)

    Prashant Bhatt
    Sr Engineer - Application Programming

  • jcrawf02

    SSC-Insane

    Points: 24198

    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."

  • paul.knibbs

    SSCoach

    Points: 15270

    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 42 total)

You must be logged in to reply to this topic. Login to reply