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.
Example:
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%'
BUT
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" ??
/P
Example:
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%'
BUT
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" ??
/P
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%')
( itemtext like '%gal%' AND itemtext like '%s8%' AND itemtext like '%sam%')
ASKER
Hi,
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
/P
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
/P
Ah yes, CONTAINSTABLE and FREETEXTTABLE
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 :)
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 :)
ASKER
After some Google on the answer from Mark I stumbled upon FULL TEXT SEARCH, and this was what I needed.
What is wrong with ?
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....