How to query text column(s) using a Table Valued Parameter?

Question

I have a Table Valued Parameter (@KEYWORD) which just has one column with 0 to many rows of keywords that will query against one or two database (nvarchar) columns (REMARKS and SUPPLEMENTAL_REMARKS). Super simple concept.

What I am having trouble with is how to write the SQL to have it check for each of the individual keywords supplied against that one (or two) database columns.

Here's my unfinished part of the WHERE clause...

WHERE
(
    CASE WHEN EXISTS (SELECT 1 FROM @KEYWORD) THEN
        --check that some combination of either REMARKS or SUPPLEMENTAL_REMARKS contains all supplied values
        CASE WHEN (
        --  RES.REMARKS (or RES.SUPPLEMENTAL_REMARKS) LIKE %keyword[0]%
        --  AND
        --  RES.REMARKS (or RES.SUPPLEMENTAL_REMARKS) LIKE %keyword[1]%
        --  AND
        --  ... (this doesn't work for many reasons, but is just to give an idea)
        ) THEN
            1
        ELSE
            0
        END
    ELSE --TVP (@KEYWORD) not supplied, so ignore this filter
        1
    END
) = 1

Hafthor · Answer 1 · 2013-01-09T18:50:42.840

1

Can you use a JOIN? Then DISTINCT back to just the source table, so you don't get multiple rows of it when you match on multiple keywords.

select distinct res.*
from sourcetable res 
join @keywords kw on res.REMARKS like '%'+kw.keyword+'%' or res.SUPPLEMENTAL_REMARKS like '%'+kw.keyword+'%'

If you need all to match and just show all source rows when keywords empty

select res.id, res.REMARKS, res.SUPPLEMENTAL_REMARKS from (
  select res.id, res.REMARKS, res.SUPPLEMENTAL_REMARKS
  from sourcetable res
  join @keywords kw on res.REMARKS like '%'+kw.keyword+'%' or res.SUPPLEMENTAL_REMARKS like '%'+kw.keyword+'%'
  group by res.id, res.REMARKS, res.SUPPLEMENTAL_REMARKS
  having count(*) = (select count(*) from @keywords)
) k1 UNION ALL SELECT res.id, res.REMARKS, res.SUPPLEMENTAL_REMARKS FROM (
  SELECT * WHERE 0=(select count(*) from @keywords)
) k0

edited Jan 09 '13 at 18:50

answered Jan 08 '13 at 23:30

Hafthor

16,358
9
56
65

I changed a few things to be MS SQL SERVER compliant and it looks like this works after testing. However, using an empty @keywords TVP returns zero matches, which is not what I want and why I put it in the WHERE clause using the CASE statement to accomodate that scenario. Any ideas how to add that? – johntrepreneur Jan 09 '13 at 01:09
add UNION ALL SELECT * FROM (SELECT *res.fieldlist* WHERE 0=(select count(*) from @keywords)) k0 – Hafthor Jan 09 '13 at 03:22
Not following. I tried to add that at the end, but got it gave me errors on SQL Fiddle. Maybe my conversion to MS SQL 2008 R2 isn't correct, but it's complaining about fieldlist so I just used * instead. However, after changing that in your last comment (adding the UNION ALL) it still gives error. – johntrepreneur Jan 09 '13 at 18:42
edited my answer to add that. yeah, it was a little more complicated than i said. – Hafthor Jan 09 '13 at 20:56

Conrad Frix · Accepted Answer · 2013-01-09T03:19:19.340

Base on the SQL comment below you only want records from res where all the keyword match on either REMARKS or SUPPLEMENTAL_REMARKS.

 --  REMARKS (or SUPPLEMENTAL_REMARKS) LIKE %keyword[0]%
        --  AND
        --  REMARKS (or SUPPLEMENTAL_REMARKS) LIKE %keyword[1]%
        --  AND
        --  ... (this doesn't work for many reasons, but is just to give an idea)

The easiest way to do that is JOIN and do a count and make sure it matches the number of keywords in @KEYWORD

DECLARE @KEYWORDCOUNT as INT
SELECT @KEYWORDCOUNT = COUNT(*) FROM @Keyword

SELECT ID, [REMARKS], [SUPPLEMENTAL_REMARKS]
FROM 
  res r
  INNER JOIN @Keyword k
  ON r.REMARKS like '%' + k.keyword + '%'
     OR r.SUPPLEMENTAL_REMARKS like '%' + k.keyword + '%'


GROUP BY 
  ID,[REMARKS], [SUPPLEMENTAL_REMARKS]
HAVING COUNT(ID) = @KEYWORDCOUNT

One way for it into your where is like so

WHERE
  @KEYWORDCOUNT = 0 
  OR 
  res.id in (SELECT ID
            FROM 
                res r
                INNER JOIN @Keyword k
               ON r.REMARKS like '%' + k.keyword + '%'
                 OR r.SUPPLEMENTAL_REMARKS like '%' + k.keyword + '%'
             GROUP BY 
              ID
             HAVING COUNT(ID) = @KEYWORDCOUNT)

You might want to consider passing @KEYWORDCOUNT in as a parameter since DataTable.Rows.Count almost free

DEMO

+1 effort and setting up the demo, but it's not working as just searching for one row with say 'Key11' yeilds zero matches due to having clause. Also searching for 3 rows each with respective values of 'Key3', 'Key4', and 'Key5' isn't working either and returns zero results. There's more, but it needs tweaking. — johntrepreneur, Jan 09 '13 at 00:34
@johntrepreneur I hardcoded the value of the keywords. I've updated the answer and the demo. You might want to consider passing it as a parameter since DataTable.Rows.Count is already available — Conrad Frix, Jan 09 '13 at 03:14
Great Thanks! It works well! And thanks for adding in WHERE clause part. — johntrepreneur, Jan 09 '13 at 19:58

How to query text column(s) using a Table Valued Parameter?

2 Answers2