SQL SERVER IN query between two comma separated column using split method

Split Method for split column value . . .


CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
        declare @idx int
        declare @slice varchar(8000)
   

        select @idx = 1
                if len(@String)<1 is="is" nbsp="nbsp" null="null" or="or" p="p" return="return" tring="tring">   

        while @idx!= 0
        begin
                set @idx = charindex(@Delimiter,@String)
                if @idx!=0
                        set @slice = left(@String,@idx - 1)
                else
                        set @slice = @String
               

                if(len(@slice)>0)
                        insert into @temptable(Items) values(@slice)


                set @String = right(@String,len(@String) - @idx)
                if len(@String) = 0 break
        end
return
end
__________________________________________________________________________


WITH testdata
(
CommaColumn,
I_AddContest_Id,
I_ModeratorID,
S_Moderator,
S_ContestTitle,
S_Contest_Image,
S_Brief_Desc,
I_Hierarchy_Detail_ID,
I_Hierarchy_Master_ID
)
AS
(

SELECT
(',' + cast(I_ModeratorID as varchar) +',' + S_ZoneID +','+ S_AreaId +','+ S_CityId +','+ S_CentreID +',') as [FK_I_Hierarchy_Detail_ID],
A.I_AddContest_Id,
A.I_ModeratorID,
A.S_Moderator,
A.S_ContestTitle,
A.S_Contest_Image,
A.S_Brief_Desc,
B.I_Hierarchy_Detail_ID,
B.I_Hierarchy_Master_ID
FROM PULSEMKTG.T_Contest_Master AS A
LEFT OUTER JOIN T_User_Hierarchy_Details AS B
ON B.I_Hierarchy_Master_ID = A.I_ModeratorID
WHERE
A.Active=1
AND A.Approve=0 AND B.I_Status <> 0
AND GETDATE() >= ISNULL(B.Dt_Valid_From,GETDATE())
AND GETDATE() <= ISNULL(B.Dt_Valid_To,GETDATE())
AND B.I_User_ID = @I_User_ID  
)

SELECT
MAX(I_AddContest_Id) AS I_AddContest_Id  
,MAX(I_ModeratorID) AS I_ModeratorID
,MAX(S_Moderator) AS S_Moderator
,MAX(S_ContestTitle) AS S_ContestTitle  
,MAX(S_Contest_Image) AS S_Contest_Image
,MAX(cast(S_Brief_Desc as varchar(max))) AS S_Brief_Desc
FROM
(
SELECT D.items AS SplitValue,* FROM testdata AS C
CROSS APPLY dbo.Split(C.CommaColumn,',') AS D
WHERE
C.I_Hierarchy_Detail_ID = D.items
OR
C.I_Hierarchy_Master_ID = C.I_ModeratorID
) AS E

GROUP BY E.I_AddContest_Id
ORDER BY E.I_AddContest_Id DESC