Comma Separated Keywords

Question

A search string gets passed to a stored procedure we have, which is, for example

"this is a search string".

Now, the query for searching is something like this:

Select * From Table Where Keywords Like '%this is a search string%'

The "Keywords" column in the database stores values comma separated, so in this case:

"this, is, a, search, string"

Obviously the query above will return no results and I've spent this afternoon figuring out how I can do this.

This is one of many many problems that arise when you store multiple values in a single column, consider a design where each keyword is in its own row in a keywords table and a one-to-many table is used to stitch things together. — Alex K., Jan 21 '16 at 16:01
Hi Alex, this is within my plans for the future as I have inherited this project unfortunately. — Geoff Tew, Jan 21 '16 at 16:05
Not technically a duplicate, but your answer is here: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Tab Alleman, Jan 21 '16 at 16:05
Take a look at this answer here: http://stackoverflow.com/a/27836213/1176870 — Chris Stillwell, Jan 21 '16 at 16:13
@ChrisStillwell while that link will work the split function found there is absolutely the worst way to write one. The performance of using a while loop in a splitter is horrendous. For some better alternatives please take a look at this article. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Jan 21 '16 at 16:16

score 1 · Answer 1 · answered Jan 21 '16 at 16:10

As mentioned in a comment, this is the wrong way to store keywords. You should have a table with one row per entity and one row per keyword.

Sometimes, we are stuck with other people's bad design decisions. If so, you can Google for a split functions and do something like:

Select *
From Table t cross apply
     (select ltrim(rtrim(item)) as keyword
      from dbo.split(t.keywords, ',')
     ) tk cross apply
     (select ltrim(rtrim(@item)) as keyword
      from dbo.split(@Keywords)
     ) input
where input.keyword = tk.keyword;

This gives you the matches. If you want to and or or them (which your question doesn't specify), then you would use aggregation on the primary key of table and include the appropriate having clause.

score 1 · Accepted Answer · answered Jan 21 '16 at 16:24

Using the function found here: http://sqlperformance.com/2012/07/t-sql-queries/split-strings

CREATE FUNCTION dbo.SplitStrings_Moden
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;

you can do something like this

SELECT a.pk, COUNT(*) AS hits
FROM Table AS a
CROSS APPLY dbo.SplitStrings_Moden(a.Keywords, ',') b
WHERE 
    CHARINDEX(b.Item, 'this is a search string') > 0
GROUP BY a.pk
ORDER BY COUNT(*) DESC

Basically you are creating a broken out table of Keyword values. You then search to see which contain the Keyword in the search string, group by the primary key and order by hits.

Thank you, this seems to work. I'll work with this and look at recreating how this is setup. — Geoff Tew, Jan 21 '16 at 16:33
This is better but if you read that article you will realize this comes from Jeff Moden's splitter and Aaron modified it so it would fit with the rest of his testing. The problem is that when you convert the input to varchar(MAX) the performance suffers horribly. The original article from Jeff can be found here. http://www.sqlservercentral.com/articles/Tally+Table/72993/ — Sean Lange, Jan 21 '16 at 16:34

Comma Separated Keywords

2 Answers2