Inserting the return from a split function to a table

  • I need to send a comma delimited string to my split function and then iterate through the results of that function and act on each element of it. Normally I create a temporary table, fill it with the collection I need to work with by way of a query, and then iterate through the records until it is empty. With the function I am unsure how to reference the table that is returned in order to cycle through the records.

    The method I am accustomed to is:

    DECLARE @Value varchar(100)

    BEGIN

    CREATE TABLE #temp (

    Value varchar(100)

    )

    INSERT INTO #temp SELECT value FROM table WHERE condition.

    SET @Value = (SELECT MAX(Value)FROM #temp)

    WHILE @Value > '' OR @Value IS NOT NULL

    BEGIN

    [TASK ALGORITHM]

    DELETE FROM #temp WHERE Value = @Value

    SET @Value = (SELECT MAX(Value)FROM #temp)

    END

    END

    The value stored in the table in this example is always unique making an ID column unnecessary.

    For some reason I am not seeing what I need to do to process the return from a function in a similar manner. Can anyone help me with this?

  • You may want to check out the thread over here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 which talks all about split functions and the best way to handle the scenario. I have a feeling you may want to copy one of the split functions posted there.

  • Thank you for your reply. Unfortunately it still doesn't provide a solution to my particular problem. Its not the split that I am having difficulty with, but how to manipulate the value that is returned from it.

  • OK I think I see what you are asking now. You could try something like this if I am understanding you correctly.

    DECLARE @result TABLE( data varchar(100) NULL )

    INSERT INTO @result SELECT data FROM dbo.ufnSplit('my,comma,delimited,string')

    SELECT * FROM @result

    Then just iterate through the @result table variable how you would with a temp table.

  • Thank you very much!

  • Out of curiosity, what kind of action do you need to take on each result?

  • Thomas Mick (3/3/2009)


    Thank you for your reply. Unfortunately it still doesn't provide a solution to my particular problem. Its not the split that I am having difficulty with, but how to manipulate the value that is returned from it.

    Hi Thomas... any chance of you posting your split function? Might have a performance tip or two for you...

    --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 7 posts - 1 through 6 (of 6 total)

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