Search code examples
sqlsql-serverrelational-division

I need to use a comma delimited string for SQL AND condition on each word

So I have a parameter. Lets say:

@Tags = 'Red,Large,New'

I have a field in my table called [Tags]

Lets say one particular row that field contains "Red,Large,New,SomethingElse,AndSomethingElse"

How can I break that apart to basically achieve the following logic, which I'll type for understanding but I know is wrong:

SELECT * FROM MyTable WHERE
Tags LIKE 'FirstWordInString'
AND Tags Like 'SecondWordInString'
AND Tags Like 'ThirdWordInString'

But it knows where to stop? Knows if there's just one word? Or two? Or three?

Workflow:

Someone clicks a tag and the dataset is filtered by that tag. They click another and the tag is appended to the search box and the dataset is then filtered by both of those tags, etc.

Thank you!

Update:

This is a product based situation.

  1. When a product is created the creator can enter search tags separated by commas.
  2. When the product is inserted, the search tags are inserted into a separate table called ProductTags (ProductID, TagID)

So, in the Product table, I have a field that has them separated by string for display purposes in the application side, and these same tags are also found in the ProductTag table separated by row based on ProductID.

What I'm not understanding is how to place the condition in the select that delivers results if all the tags in the search exist for that product.

I thought it would be easier to just use the comma separated field, but perhaps I should be using the corresponding table (ProductTags)

Solution

  • One relational division approach using the ProductTags table is

    DECLARE @TagsToSearch TABLE (
  TagId VARCHAR(50) PRIMARY KEY )

INSERT INTO @TagsToSearch
VALUES      ('Red'),
            ('Large'),
            ('New')

SELECT PT.ProductID
FROM   ProductTags PT
       JOIN @TagsToSearch TS
         ON TS.TagId = PT.TagId
GROUP  BY PT.ProductID
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM   @TagsToSearch)

    The @TagsToSearch table would ideally be a table valued parameter passed directly from your application but could also be populated by using a split function against your comma delimited parameter.