Search in Coma delimited string

Search in Coma delimited string

 

--SELECT [dbo].[SearchinComaString]('2,3,5',6)

CREATE FUNCTION [dbo].[SearchinComaString]

(

       @Comastring nvarchar(max),

       @searchval bigint

)

RETURNS bigint

AS

BEGIN

       DECLARE @Exist bigint

       DECLARE @x XML

--set @Comastring ='2,3,5'

SELECT @x = cast('<A>'+ replace(@Comastring,',','</A><A>')+ '</A>' AS xml)

 

SELECT @Exist =

CASE WHEN @searchval IN

(

SELECT t.value('.', 'varchar(max)')

        FROM @x.nodes('/A') AS x(t)) THEN 1

                ELSE 0

                END 

       RETURN @Exist

 

END