Select with LIKE and multiple search words

I have an issue with selecting from a table where I want to select all records where ALL the words I specify is present.
Record 1: "samsung galaxy s8 black"
Record 2: "samsung note 8 gold"

If the search parameter is: "sam gal s8" I want to ONLY get record 1 and this work if I build the LIKE '%sam%%gal%%s8%'
If I use EXIST I will get both this records
I also want to get the same result if I search for '%gal%%s8%%sam%' but I have not found a solution for this.

The solution I'm looking for is the same as if I used this in the WHERE clause:
        ( isnull(itemtext,'') like '%gal%' AND  isnull(itemtext,'') like '%s8%' AND  isnull(itemtext,'') like '%sam%')

Do anybody have a solution for this without having to use temp tables and "exec sp_executesql" ??

Petter A. HalsethAsked:
Who is Participating?
Petter A. HalsethConnect With a Mentor Author Commented:
Hi, I have a SP that have several other search parameters, but these are mainly INT or single search. The solution I have working today uses sp_executesql because I build the search string in a function. This @itemsearch is what the user inputs as a search. And could be whatever they want to search for, so it can be one or as many word's as the uses enters.

I will check out the patindex and see if I can make some dynamic "thing" around this...
Mark WillsTopic AdvisorCommented:
Not sure I understand the requirement....

What is wrong with ?

The solution I'm looking for is the same as if I used this in the WHERE clause:
 ( isnull(itemtext,'') like '%gal%' AND  isnull(itemtext,'') like '%s8%' AND  isnull(itemtext,'') like '%sam%')

Why mention temp tables and exec sp_executesql ?

How are those search keywords being generated ?

If you could please elaborate a little more
You may need to delimit your terms '%gal% %s8% %sam%' except there is a sequence....
Scott PletcherSenior DBACommented:
The only really workable method is to use the ANDs in the WHERE, although you don't need the ISNULLs:

 ( itemtext like '%gal%' AND  itemtext like '%s8%' AND itemtext like '%sam%')
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Petter A. HalsethAuthor Commented:
Sorry for confusting with temp tables, that was another case...
But for this case: I have a stored procedure where one of the parameters is @itemsearch. Today I have a function who splits up the search string and create the string I use in the sql: "( isnull(itemtext,'') like '%gal%' AND  isnull(itemtext,'') like '%s8%' AND  isnull(itemtext,'') like '%sam%')"
With this string I finish the sql and execute it using : sp_executesql

But I'm trying to manage this way of searching without have to use sp_executesql
Mark WillsConnect With a Mentor Topic AdvisorCommented:
I cant think of a reason  why you would need to use sp_executesql unless it is a very generic procedure applicable to different tables / different columns. Then of course you would create a dynamic SQL routine.

For example, we could use things like PATINDEX
declare @parm1 varchar(100) = '%s8%'
declare @parm2 varchar(100) = '%sam%'
declare @parm3 varchar(100) = '%bla%'
declare @str varchar(250) = 'samsung galaxy s8 black'

select patindex(@parm1,@str),patindex(@parm2,@str),patindex(@parm3,@str)

select case when patindex(@parm1,@str) > 0 and patindex(@parm2,@str) > 0 and patindex(@parm3,@str) > 0
            then 'Found : '+@str
            else 'Nadda'
            end as [Search Success]

Open in new window

We can embed the %s8% inside a variable (or column or whatever) and use that to search. Also, check out the CASE example where @str could be your column itemtext

Obviously this is a nasty little example, but works all the same, and could also be used in a where clause.

But before we can turn it into a solution, what are the parameters ?
How many Search items could there be (ie like my @parm1 in the example above) ?
Is the search always in the format %this%%that%%other%

Any way if you can get back, it would be appreciated.
Petter A. HalsethAuthor Commented:
Hi Mark,
I actually found a solution to this issue. I did try out your suggestion, but when I did some Google search on this I stumbled upon "CONTAINSTABLE" and full text search.
And this did the trick :-)

Thanks for assisting

Mark WillsTopic AdvisorCommented:

Can be frustrating at times, wildcard is an asterix and only at the end (e.g sam* will return samsung and samsonite, but *sam* wont)

so best used when matching whole words.

But has thesaurus and inflectional as formsof which are very good.

Would love to hear back as to how it works for you :)
Petter A. HalsethAuthor Commented:
After some Google on the answer from Mark I stumbled upon FULL TEXT SEARCH, and this was what I needed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.