ASP.NET Single Text Box Multiple Keyword Search

Question

Right now, I have:

Upper(tb.name) LIKE Upper('%' + :SearchBox + '%')

With this, I can type a single keyword and if it exists in tb.name, it will return in the results. I would like to be able to type in multiple keywords in the SearchBox, separated by spaces, and return results that contain ALL keywords.

Example: I type in "Ford" in the SearchBox and am returned

Name
2015 Ford SUV Blue
2016 Ford SUV Black
2017 Ford SUV Blue

I would like to be able to type in "Ford Blue" (or any other amount of keywords) to return

Name
2015 Ford SUV Blue
2017 Ford SUV Blue

Any keywords input would be separated by a space. So, if I type in "SUV Blue 2017" it would be "SUV" AND "Blue" AND "2017". Any help is greatly appreciated!

Possible duplicate of [Combining "LIKE" and "IN" for SQL Server](https://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server) — hardkoded, Nov 19 '17 at 22:17
Not sure to how accomplish the overall goal of taking multiple user entered words from the single text box and building them into the WHERE clause. My statement is much larger than this, I just used an example. — scedsh8919, Nov 19 '17 at 22:22
There are many examples arround in the community https://stackoverflow.com/questions/43601570/how-to-make-a-search-query-where-contain-each-words-of-string-in-sql-server — hardkoded, Nov 19 '17 at 22:26

score 0 · Answer 1 · answered Nov 19 '17 at 23:16

You need to split your keyword string and find rows where all of them exist. Something like this (see comments in the code).

declare @keywords varchar(max)='ford blue'

--build a series of CTE
;with split as ( --split keyword string into table
select '%'+value+'%' v from string_split(@keywords,' ')
),
cnt as ( -- always single value
select count(*) cnt from split
),
prod as(
select p.* ,
COUNT(t.v) over(partition by p.name) cnt,
ROW_NUMBER() over(partition by p.name order by p.name) rn
from tb p
--where ProductName like all -- illegal and never work
inner join split t on p.ProductName like t.v
)
select * from prod
cross join cnt -- don't afraid of Cartesian product here
where prod.cnt = cnt.cnt -- all keywords found
and rn=1 -- no duplicates

score 0 · Answer 2 · answered Nov 20 '17 at 05:26

Put % to your blank string, for example:

WITH TB AS(
     SELECT 'Ford SUV Blue' NAMES FROM DUAL UNION ALL
     SELECT 'Ford SUV GREEN' NAMES FROM DUAL UNION ALL
     SELECT 'SOME thing there' FROM DUAL 
)
SELECT * FROM TB H
WHERE Upper(H.NAMES) LIKE Upper('%SOME% %thing%');

ASP.NET Single Text Box Multiple Keyword Search

2 Answers2