3

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)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user1447679
  • 3,076
  • 7
  • 32
  • 69
  • 2
    This becomes _a lot_ easier _and_ quicker if you normalise your database... – Ben Aug 25 '13 at 21:30
  • @Ben - Relational division queries aren't that much (if at all) quicker and easier *with* a normalised structure. Not that I'm advocating this design just the OP may be disappointed if they redesign the DB and are expecting the query to be trivial. – Martin Smith Aug 25 '13 at 21:37
  • @Ben Updated my question to bring more clarity. I have normalized the database. – user1447679 Aug 25 '13 at 21:40
  • I would suggest you write a SQL function that can (safely!) construct the query dynamically, and pass it an array of tags. – matthudson Aug 25 '13 at 21:43
  • This `ProductTags` table. What is the `TagId`? Is it a string such as `Red` or is there another table called `Tags` or something? – Martin Smith Aug 25 '13 at 21:46
  • @matthudson That easily flew over my head... at least for now. :) – user1447679 Aug 25 '13 at 21:46
  • @MartinSmith ProductTable has a field called Tags which is a comma separated list. ProductTagsTable has the same tags as the first, separated by row (TagID, ProductID). When an insert is created, it adds any "NEW" tags that don't already exist by name, and adds the new product for each row (TagID, ProductID) for as many tags as were entered. – user1447679 Aug 25 '13 at 21:48
  • And what version of SQL Server are you on? If 2008+ would you consider passing the parameters in via a table valued parameter rather than comma separated list? – Martin Smith Aug 25 '13 at 21:51

3 Answers3

2

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

You just need to add wildcards:

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

UPDATE: I understand now the problem is that you have a variable you are trying to match the tags to. The variable has the string list of tags. If you split the list of user selected tags using a split string function, or can get the user selected tags from input form separately, then you can just use:

SELECT *
FROM MyTable
WHERE ',,'+Tags+',' LIKE '%,'+@tag1+',%'
         AND ',,'+Tags+',' LIKE '%,'+@tag2+',%'
         AND ',,'+Tags+',' LIKE '%,'+@tag3+',%'
         AND ',,'+Tags+',' LIKE '%,'+@tag4+',%'

etc.

This will work for any number of entered tags, as an empty @tag would result in the double comma appended to the tags field matching '%,,%'.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
0

From codeproject: 

-- =============================================
-- Author: Md. Marufuzzaman
-- Create date: 
-- Description: Split an expression. 
-- Note: If you are using SQL Server 2000, You need to change the 
-- length (MAX) to your maximum expression length of each datatype.
-- =============================================
/*
SELECT * FROM [dbo].[SPLIT] (';','I love codeProject;!!!;Your development resources')
*/
CREATE FUNCTION [dbo].[SPLIT] 
(  @DELIMITER VARCHAR(5), 
  @LIST      VARCHAR(MAX) 
 ) 
   RETURNS @TABLEOFVALUES TABLE 
  (  ROWID   SMALLINT IDENTITY(1,1), 
     [VALUE] VARCHAR(MAX) 
  ) 
AS 
BEGIN

  DECLARE @LENSTRING INT 

  WHILE LEN( @LIST ) > 0 
     BEGIN 

        SELECT @LENSTRING = 
           (CASE CHARINDEX( @DELIMITER, @LIST ) 
               WHEN 0 THEN LEN( @LIST ) 
               ELSE ( CHARINDEX( @DELIMITER, @LIST ) -1 )
            END
           ) 

        INSERT INTO @TABLEOFVALUES 
           SELECT SUBSTRING( @LIST, 1, @LENSTRING )

        SELECT @LIST = 
           (CASE ( LEN( @LIST ) - @LENSTRING ) 
               WHEN 0 THEN '' 
               ELSE RIGHT( @LIST, LEN( @LIST ) - @LENSTRING - 1 ) 
            END
           ) 
     END

  RETURN 

END

http://www.codeproject.com/Articles/38843/An-Easy-But-Effective-Way-to-Split-a-String-using

Oscar
  • 13,594
  • 8
  • 47
  • 75