Split string using Tally Table

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

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

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

  • Nice question..

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

    [font="Verdana"]Regards,
    Rals
    [/font].
  • [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

  • 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 😀

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

  • 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/

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

  • hugo explanation is good

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

  • 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/

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

  • Got it right...

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

    Prashant Bhatt
    Sr Engineer - Application Programming

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

  • 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