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 — hardkoded
– hardkoded, Commented Nov 19, 2017 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
– scedsh8919, Commented Nov 19, 2017 at 22:22
There are many examples arround in the community stackoverflow.com/questions/43601570/… — hardkoded
– hardkoded, Commented Nov 19, 2017 at 22:26

Alex Kudryashev · Accepted Answer · 2017-11-19 23:16:39Z

0

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

answered Nov 19, 2017 at 23:16

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hong Van Vit · Accepted Answer · 2017-11-20 05:26:39Z

0

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%');

answered Nov 20, 2017 at 5:26

Hong Van Vit

3,0063 gold badges22 silver badges45 bronze badges

Collectives™ on Stack Overflow

ASP.NET Single Text Box Multiple Keyword Search

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related