Inserting the return from a split function to a table

Question

Inserting the return from a split function to a table

Thomas Mick

SSC Enthusiast

Points: 127
More actions
March 3, 2009 at 11:09 am

#155361

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?

Viewing 7 posts - 1 through 6 (of 6 total)

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

tcnolan SSCrazy Points: 2097 More actions · Answer 1

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.

Thomas Mick SSC Enthusiast Points: 127 More actions · Answer 2

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.

tcnolan SSCrazy Points: 2097 More actions · Answer 3

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.

Thomas Mick SSC Enthusiast Points: 127 More actions · Answer 4

Thomas Mick

SSC Enthusiast

Points: 127

March 3, 2009 at 1:56 pm

#952706

Thank you very much!

tcnolan SSCrazy Points: 2097 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

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)