Hello,
First you need to change your Split function as follows
USE [Practice]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 12/20/2011 16:38:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (pid int,items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
declare @ids int
select @idx = 1
if len(@String)<1 or @String is null return
SET @ids =0
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
BEGIN
set @slice = left(@String,@idx - 1)
SET @ids = @ids + 1
END
else
BEGIN
set @slice = @String
SET @ids = @ids + 1
END
if(len(@slice)>0)
insert into @temptable(pid,Items) values(@ids,@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
After then retive data as per your requiremnt you need to store data into temp table as follows
DECLARE @Product AS varchar(max)
SET @Product ='%a%/%offe%/%il%'
DECLARE @Delimater AS char(1)
SET @Delimater='/'
DECLARE @imax INT, @i INT = 1
CREATE TABLE #TempProduct
(
ProductID int,
ProductName char(30)
)
SELECT @imax= COUNT(*) from dbo.split(@Product,@Delimater)
WHILE (@i <= @imax)
BEGIN
INSERT INTO #TempProduct (ProductID, ProductName)
SELECT ProductID, ProductName
FROM Product
WHERE ProductName LIKE
(
select items from dbo.split(@Product,@Delimater) WHERE pid = @i
)
SET @i = @i + 1
END
SELECT * FROM #TempProduct
Drop Table #TempProduct
Hope this is helpful !
Thanks