Help modify a UDF returning the Nth value of a CSV

[tokuzumi]

I have a UDF that takes a CSV, and puts each value into a temp table, and gives it an incremental value. So a string "3,6,9", will look like this;

Id Value
1 3
2 6
3 9

What I want the UDF to do, is using the @Id parameter in the UDF, select out the row that matches the @Id value. So if I wanted the Value where Id = 2, I would get a value of 6. Thanks for any suggestions.

Here is the code:

Create Function dbo.NthValOfCSV ( @Id int, @Array varchar(1000))
returns @IntTable table
(
Value int,
IntValue int
)
AS
begin

declare @separator char(1), @i int
set @separator = ','
set @i = 0

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

set @i = @i + 1
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (@i, Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
--select IntValue from @IntTable where Value = @Id
end

return
end

 

[JohnDTampaBay]

Since a table is currently being returned from the procedure, add

DELETE FROM @IntTable WHERE Value <> @Id

just before the RETURN statement. You should now get a single row table with only the ID requested. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[tokuzumi]

That was almost too easy! Thank you very much!