How to find multiple words in a string in SQL

Question

I want to be able to take a users search input and find records that match in SQL.

I'm currently using Name like '%' + @SearchText + '%' ) which works fine if they enter in the text the correct way.

Example

If they search for "Jaws Revenge" or "Jaws 2 the revenge" or "revenge jaws" they won't get any results. I want it to return 1 result, id #2 from the table below.

Movies Table
---------------------
ID  Name
1   Jaws
2   Jaws 2: The revenge
3   Jaws 3-D
4   Rocky 5000

I've read about full text search but I don't know if that's worth it, the only option, or if there is a more simple solution.

I added the SQL Server tag based on the syntax. You should tag your questions with the database you are actually using. — Gordon Linoff
– Gordon Linoff, Commented Jul 30, 2017 at 13:23
As per your comment in Gordon's answer, you need FULLTEXT Index — Pரதீப்
– Pரதீப், Commented Jul 30, 2017 at 13:27
@Pரதீப் Can you provide an example or link? Also, is that available on cloud hosting like AWS or Azure? — user3953989
– user3953989, Commented Jul 30, 2017 at 13:28
It is available in Azure learn.microsoft.com/en-us/sql/t-sql/statements/… — Pரதீப்
– Pரதீப், Commented Jul 30, 2017 at 13:31

Gordon Linoff · Accepted Answer · 2017-07-30 13:22:38Z

2

You can replace spaces with %:

Name like '%' + replace(@SearchText, ' ', '%') + '%'

answered Jul 30, 2017 at 13:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user3953989 Over a year ago

This is getting closer but it doesn't work for searches like revenge jaws. I know the OP didn't state that case, sorry. I updated the op

Feras Al Sous · Accepted Answer · 2017-07-30 13:39:45Z

0

You can use like '%' + replace(@SearchText, ' ', '%') + '%' in expration

answered Jul 30, 2017 at 13:39

Feras Al Sous

1,0811 gold badge12 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to find multiple words in a string in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related