Post reply

how to split a delimeted field value into rows

  • December 16, 2009 at 1:30 pm

    #1093285

    Paul-755326 (12/14/2009)

    Jeff Moden (12/13/2009)

    Paul-755326 (12/13/2009)

    RBarryYoung (12/13/2009)

    Ouch! You can do much better than a WHILE loop. I strongly advise to read the article that bitbucket pointed out to you (http://www.sqlservercentral.com/articles/T-SQL/63003/) which as it happens is by Jeff Moden. It has much superior ways to do this.

    I shall do, but my source table only ever has one record and it is run via DTS, so it is fine for that use.

    Regards

    I absolutely agree. It's fine for "that" use.

    The problem is that if someone else ends up with a similar requirement in a multi-row environment (heh... I'm a poet and don't know it) and they find that code, they'll end up using it likely because of scheduling pressures. If they do, they'll ultimately end up with a performance nightmare.

    "Good enough" usually isn't.

    You're also missing the opportunity to learn something new about performance code.

    Hello Jeff - I very much appreciate your point and your guidance, however my original question wasn't about how to perform the split itself, but how to call functions generally. This is why I didn't initially post the code that I had found, as I didn't feel it was relevant in itself to my question and I just left it as background information.

    I tend to make very great use of online forums and have done for years. I am a veteran of other technologies and have provided help to hundrends of other people over the years in the relevant forums to that technology. I am currently a SQL novice so am not able to contribute much myself to SQL forums. I have recently posted another question on this forum which went unanswered. Luckily I was able to solve the problem myself and I left the solution to my own question as a response in case other had the same problem.

    The difficulty I have been having in trying to learn these techniques was in being able to practicably apply solutions and examples provided by others. I unfortunately don't have the luxury of working for a company with large training budgets 🙁 I also have a go-live deadline looming which is not leaving me much time to investigate thins properly. Improvements and efficiency gains will come later.

    I am very likely to perform more of this sort of split in the future (much of my source data comes from Domino which can use multi-value fields), and as and when I do I will investigate the more efficient methods that you have pointed out.

    Thank you once again for your assistance. I hope to gain from it again in the future and eventually contribute myself.

    Kind regards

    Paul

    Heh... what's really amazing about this whole thing, Paul, is that it probably took you more time to write that excuse for doing it incorrectly than it would have taken you do actually do it correctly. 😀

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

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing post 16 (of 15 total)

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