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 «««5354555657»»»

Tally OH! An Improved SQL 8K “CSV Splitter” Function Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 2:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:32 PM
Points: 1,787, Visits: 5,721
ChrisM@Work (2/27/2013)

...

About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.


I know what you mean, at least you are in a "safe place" here

It's worse when you make the silly mistakes in front of a client, insisting something is wrong or right when it patently isn't


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1424408
    Posted Wednesday, February 27, 2013 3:28 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: 2 days ago @ 9:43 AM
    Points: 7,284, Visits: 13,824
    mister.magoo (2/27/2013)
    ChrisM@Work (2/27/2013)

    ...

    About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.


    I know what you mean, at least you are in a "safe place" here

    It's worse when you make the silly mistakes in front of a client, insisting something is wrong or right when it patently isn't


    I know of at least one other old git who will be cackling his face off reading these posts


    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
    Exploring Recursive CTEs by Example Dwain Camps
    Post #1424436
    Posted Wednesday, February 27, 2013 3:30 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Yesterday @ 8:01 AM
    Points: 11,194, Visits: 11,165
    ChrisM@Work (2/27/2013)
    I know of at least one other old git who will be cackling his face off reading these posts

    Oi!




    Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1424438
    Posted Wednesday, February 27, 2013 3:34 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: 2 days ago @ 9:43 AM
    Points: 7,284, Visits: 13,824
    Paul White (2/27/2013)
    ChrisM@Work (2/27/2013)
    I know of at least one other old git who will be cackling his face off reading these posts

    Oi!


    Heh I didn't mean you specifically Paul, though you do have some history of spotting my embarrassing coding faux pas
    Anyway, isn't it past your bedtime?


    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
    Exploring Recursive CTEs by Example Dwain Camps
    Post #1424441
    Posted Wednesday, February 27, 2013 3:43 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Yesterday @ 8:01 AM
    Points: 11,194, Visits: 11,165
    ChrisM@Work (2/27/2013)
    Anyway, isn't it past your bedtime?

    We can no longer be friends




    Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1424445
    Posted Wednesday, February 27, 2013 3:51 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: 2 days ago @ 9:43 AM
    Points: 7,284, Visits: 13,824
    Paul White (2/27/2013)
    ChrisM@Work (2/27/2013)
    Anyway, isn't it past your bedtime?

    We can no longer be friends


    Slippers are in the post


    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
    Exploring Recursive CTEs by Example Dwain Camps
    Post #1424449
    Posted Wednesday, February 27, 2013 5:40 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Yesterday @ 10:52 PM
    Points: 37,056, Visits: 31,619
    ChrisM@Work (2/27/2013)
    mister.magoo (2/27/2013)
    ChrisM@Work (2/27/2013)

    ...

    About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.


    I know what you mean, at least you are in a "safe place" here

    It's worse when you make the silly mistakes in front of a client, insisting something is wrong or right when it patently isn't


    I know of at least one other old git who will be cackling his face off reading these posts


    Heh... nah... I've BIN there myself.


    --Jeff Moden
    "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1424476
    Posted Wednesday, March 13, 2013 9:29 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Wednesday, June 25, 2014 8:16 PM
    Points: 9, Visits: 47
    Is there a reason that you mention subtracting the length of the final string from 8000 like so
    ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1), 0), 8000) - s.N1

    and then in the final code you have
     ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)

    I just wanted to make sure I'm understanding the code correctly. It looks like it runs the same either way...
    Post #1430722
    Posted Monday, March 18, 2013 2:46 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: 2 days ago @ 1:53 PM
    Points: 136, Visits: 614
    Did I miss something? I didn't have the patience to go over all 54 pages of replies. Initially when the code for the cteTally was changed to be faster it used a

    SELECT 0 UNION ALL
    ...

    However in the final function it unions with 1 instead of 0. I tried both versions in the final function and they bot work actually.



    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

    Post #1432347
    Posted Wednesday, June 26, 2013 7:24 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Friday, June 13, 2014 8:32 AM
    Points: 24, Visits: 88
    Late entry to the discussion. I ran into an issue with a similar string splitter, and tried to break down my findings on this.

    The following code causes an error in SQL Server 2008R2:
    DECLARE @VALUES VARCHAR(8000)
    SET @VALUES = 'E62|E48|E47'

    SELECT
    Item
    FROM dbo.[DelimitedSplit8K](@VALUES, '|')
    WHERE ISNUMERIC(RIGHT(Item, LEN(Item) - 1)) > 0

    The following works
    DECLARE @VALUES VARCHAR(MAX)
    SET @VALUES = 'E62|E48|E47'

    SELECT
    Item, ItemNumber
    FROM dbo.[DelimitedSplit8K](@VALUES, '|')
    WHERE ISNUMERIC(RIGHT(Item, LEN(Item) - 1)) > 0

    Select * also works, as does only ItemNumber.

    The odd thing is that none of the possible result sets give an Item with a length of 0 (that would result in the error). I think something gets optimized so that an error is prematurely raised.

    If I add one more CTE level to the end of the splitter function, specifying that ItemNumber > 0, this error does not occur.

    Post #1467641
    « Prev Topic | Next Topic »

    Add to briefcase «««5354555657»»»

    Permissions Expand / Collapse