title: SQL SELECT where tag value LIKE
body: <p>I am attempting to make a Calendar service, within that calendar service, there are events, and events can be tagged with metadata which is searchable.</p>
  
  <p>I want to be able to search for records where all tags must exists (Mandatory Tags) and/or where any tags exist (Optional Tags).</p>
  
  <p>I have managed to create a query where this works, when the tag value matches 'exactly'. But I cannot work out how to return results where the tag value is LIKE '%value%'.</p>
  
  <p>Here is my current implementation </p>
  
  <p><strong>Tables And Data</strong></p>
  
  <pre><code>CREATE TABLE Events
  (
   Id INT,
   EventText VARCHAR(500)
  );
  
  CREATE TABLE EventDates
  (
   Id INT,
   EventId INT,
   StartDate DATETIME,
   EndDate DATETIME,
   Archived BIT
  );
  
  CREATE TABLE Tags
  (
   Id INT,
   Description VARCHAR(50)
  );
  
  CREATE TABLE EventTags
  (
   EventId INT,
   TagId INT,
   Value VARCHAR(50)
  );
  
  INSERT INTO Events VALUES (1, 'Event Name 1');
  INSERT INTO Events VALUES (2, 'Event Name 2');
  
  INSERT INTO EventDates VALUES (1, 1, '2013-01-01', '2013-01-02', 0);
  INSERT INTO EventDates VALUES (2, 1, '2013-01-07', '2013-01-08', 0);
  INSERT INTO EventDates VALUES (3, 2, '2013-01-02', '2013-01-03', 0);
  
  INSERT INTO Tags VALUES (1, 'Tag Name 1');
  INSERT INTO Tags VALUES (2, 'Tag Name 2');
  
  INSERT INTO EventTags VALUES (1, 1, 'Value 1');
  INSERT INTO EventTags VALUES (1, 1, 'Value 2');
  INSERT INTO EventTags VALUES (1, 2, 'Value 1');
  INSERT INTO EventTags VALUES (1, 2, 'Value 2');
  INSERT INTO EventTags VALUES (2, 1, 'Value 1');
  </code></pre>
  
  <p><strong>Query</strong></p>
  
  <pre><code>DECLARE @MandatoryTagXml XML
  DECLARE @OptionalTagXml XML
  DECLARE @StartDate DATETIME
  DECLARE @EndDate DATETIME
  DECLARE @SearchTypeId SMALLINT
  
  SET @StartDate = '2013-01-01'
  SET @EndDate = '2013-01-31'
  SET @SearchTypeId = 1
  
  -- Tags that it must match all of
  SET @MandatoryTagXml = '&lt;tags&gt;
                            &lt;tag&gt;
                              &lt;description&gt;Tag Name 1&lt;/description&gt;
                              &lt;value&gt;Value 1&lt;/value&gt;
                            &lt;/tag&gt;
                          &lt;/tags&gt;'
  
  -- Tags that it can match one or more of
  SET @OptionalTagXml = '&lt;tags&gt;
                            &lt;tag&gt;
                              &lt;description&gt;Tag Name 2&lt;/description&gt;
                              &lt;value&gt;Value 2&lt;/value&gt;
                            &lt;/tag&gt;
                          &lt;/tags&gt;'
  
      DECLARE @MandatoryIdTable TABLE ([EventId] BIGINT, [EventDateId] BIGINT)
      DECLARE @OptionalIdTable TABLE ([EventId] BIGINT, [EventDateId] BIGINT)
  
  IF(@MandatoryTagXml IS NOT NULL)
  BEGIN
      -- Select ids with matching mandatory tags.
      ;WITH MandatoryTags AS
       (
        SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
               TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]       
        FROM @MandatoryTagXml.nodes('/tags/tag') AS T(TagValue)
        )
  
      INSERT INTO @MandatoryIdTable
      -- Records where ALL tags match EXACTLY
      SELECT E.Id [EventId], ED.Id [EventDateId]
      FROM [dbo].[Events] E
      INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
      WHERE ED.StartDate &gt;= @StartDate
      AND ED.EndDate &lt;= @EndDate
      AND ED.Archived = 0
      AND NOT EXISTS (
                      SELECT T.Id, c.value
                      FROM MandatoryTags c JOIN Tags T
                          ON c.[description] = T.[Description]
                      EXCEPT
                      SELECT T.TagId, T.Value
                      FROM [EventTags] T
                      WHERE T.EventId = E.Id                          
                      )
  END
  ELSE -- Select All records 
  BEGIN
      INSERT INTO @MandatoryIdTable
      -- Records where ALL tags match EXACTLY
      SELECT E.Id [EventId], ED.Id [EventDateId]
      FROM [dbo].[Events] E
      INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
      WHERE ED.StartDate &gt;= @StartDate
      AND ED.EndDate &lt;= @EndDate
      AND ED.Archived = 0
  END
  
      ;WITH OptionalTags AS
       (
        SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
               TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]       
        FROM @OptionalTagXml.nodes('/tags/tag') AS T(TagValue)
        )
  
      INSERT INTO @OptionalIdTable
      -- Records ANY tags match EXACTLY
      SELECT E.Id [EventId], ED.Id [EventDateId]
      FROM [dbo].[Events] E
      INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
      WHERE ED.StartDate &gt;= @StartDate
      AND ED.EndDate &lt;= @EndDate
      AND ED.Archived = 0
      AND EXISTS (
                  SELECT T.Id, c.value
                  FROM OptionalTags c JOIN Tags T
                      ON c.[description] = T.[Description]
                  INTERSECT
                  SELECT T.TagId, T.Value
                  FROM [EventTags] T
                  WHERE T.EventId = E.Id                          
                  )
  
  -- Determine if we need to factor in optional tags in result set
  IF (@OptionalTagXml IS NOT NULL)
  BEGIN
      -- Select results that exist in both optional and mandatory tables
      SELECT DISTINCT M.*
      FROM @MandatoryIdTable M
      INNER JOIN @OptionalIdTable O ON O.EventId = M.EventId AND O.EventDateId = M.EventDateId
  END
  ELSE
  BEGIN
      -- Select results that exist in mandatory table
      SELECT DISTINCT M.*
      FROM @MandatoryIdTable M
  END
  </code></pre>
  
  <p>I have created an <a href="http://sqlfiddle.com/#!3/83b659/7">SQLFiddle Demo</a> for it.</p>
  
  <p>My idea is to use @SearchTypeId to switch between exact match searching and LIKE match searching.</p>
  
  <p>(Note I am not a DBA, so there may be better ways to do this. I am open to suggestions)</p>
  
  <p><strong>Can anyone offer suggestions as to how to get LIKE matches on tag values?</strong></p>
  
  <p>Many thanks</p>