Matching a delimited string against another delimited string

I found this is the require thing in our application, as some one in Group asked for help on this as well as one of the member of asp.net forums asked same thing but in different context.

What generally we need is, in our database one field is having multiple value separated with comma. Lets say I am sailor of Property, consider Home as property and features; a bunch of features will create feature group; so home will contains one of the feature group. So here is the feature group table.

SET NOCOUNT ON
DECLARE @FeatureGroup TABLE
(
    PropertyID INT,
    Features VARCHAR(MAX)
)

INSERT INTO @FeatureGroup VALUES(1, 'Gym')
INSERT INTO @FeatureGroup VALUES(2, 'Gym, Swimming Pool')
INSERT INTO @FeatureGroup VALUES(3, 'Swimming Pool, Terrace')
INSERT INTO @FeatureGroup VALUES(4, 'Swimming Pool, Terrace, Gym')
INSERT INTO @FeatureGroup VALUES(5, 'Swimming Pool, Gym, Parking')
INSERT INTO @FeatureGroup VALUES(6, 'Swimming Pool, Terrace, Basement')
INSERT INTO @FeatureGroup VALUES(7, 'Swimming Pool, Gym, Terrace, Basement, Parking')
SELECT * FROM @FeatureGroup 

Gives following output
---------------------------------------------------
1    Gym
2    Gym, Swimming Pool
3    Swimming Pool, Terrace
4    Swimming Pool, Terrace, Gym
5    Swimming Pool, Gym, Parking
6    Swimming Pool, Terrace, Basement
7    Swimming Pool, Gym, Terrace, Basement, Parking

Lets say we have to search for "Gym, Parking", we should list those property which contains either Gym or Parking. There are two way to achieve this, one is using SPLIT function [while is the user define function] and another is XML.

Using SPLIT Function:

Its user define function which splits the comma separated value to Table variable. In this case we first split our filter using Split function which will return the rows representation of our filter; mean each filter will be in separate row. Lets see the definition of Split function.

CREATE function [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results Table (Item1 nvarchar(100))
As    
Begin    
    DECLARE @Index int    
    DECLARE @Slice nvarchar(100)    
     
    SET @Index = 1    
    
    IF @String Is NULL Return    
    
    WHILE @Index != 0    
    BEGIN    
        SELECT @Index = CharIndex(@Delimiter, @String)    
        If @Index != 0    
            SELECT @Slice = LEFT(@String, @Index - 1)    
        else    
            SELECT @Slice = @String    
    
        INSERT INTO @Results VALUES(RTRIM(LTRIM(@Slice)))
      
        SELECT @String = RIGHT(@String, LEN(@String) - @Index)
        IF LEN(@String) = 0 BREAK    
     END    
    RETURN    
END

Here is the use of Split.

SELECT * FROM [master].[dbo].[Split] ('Gym, Parking', ',')

Item1
--------
Gym
Parking

Now lets write query which uses Split function.

DECLARE @Filter VARCHAR(100)
set @Filter = 'Gym, Parking'

select PropertyId, Features from @FeatureGroup FG WHERE -1 IN (
     SELECT CASE WHEN (PATINDEX('%' + Item1 + '%', FG.Features)) > 0 THEN -1 ELSE 0 END
     FROM [master].[dbo].[Split] (@Filter, ','))

Here is the output:
-------------------------------------------------------
1    Gym
2    Gym, Swimming Pool
4    Swimming Pool, Terrace, Gym
5    Swimming Pool, Gym, Parking
7    Swimming Pool, Gym, Terrace, Basement, Parking

And now lets do it with XML.

DECLARE @Filter VARCHAR(100)
set @Filter = 'Gym, Parking'

DECLARE @xmlFilter XML
SELECT @xmlFilter = CAST('<i>' + REPLACE(@Filter, ',', '</i><i>') + '</i>' AS XML)

SELECT DISTINCT    
    PropertyID, Features
FROM @FeatureGroup FG
CROSS JOIN (
    SELECT 
        x.i.value('.', 'VARCHAR(10)') AS filter
    FROM @XmlFilter.nodes('//i') x(i)
) b
WHERE PATINDEX('%' + b.filter + '%', features) > 0

OUTPUT:
-------------------------------------------------------
1    Gym
2    Gym, Swimming Pool
5    Swimming Pool, Gym, Parking
7    Swimming Pool, Gym, Terrace, Basement, Parking
4    Swimming Pool, Terrace, Gym

You can find the details of how XML works in here, you can read the TSQL Labs 13 - Matching a delimited string against another delimited string created by Jacob Sebastian