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