March 3, 2009 at 11:09 am
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?
March 3, 2009 at 11:51 am
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.
March 3, 2009 at 12:24 pm
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.
March 3, 2009 at 1:50 pm
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.
March 3, 2009 at 1:56 pm
Thank you very much!
March 3, 2009 at 2:11 pm
Out of curiosity, what kind of action do you need to take on each result?
March 3, 2009 at 7:16 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply