Searching for all the words
-
Hi
IN SHORT:
How do you write a search query that searches for multiple words, BUT only returns a row if ALL the words are found within a row
IN DETAILS:
I have been trying to solve this problem for a long time, but with no luck. It seems very simple, but I dont have a clue how to do it.
Basically, I want to write a search query that will search in my product table. I want to search for ALL the search keywords within the table columns.
I have following table structure:
Products (productID, name, description)
OtherInfo (ID, Manufacturer, MachineName, productID)
Now a user can enter 'monitors sensors'.
I want to search in the above tables, and only return a record if 'monitors' AND 'sensors' are found for a product.
So I may write somethign like:
Select * from Products p
Inner Join OtherInfo o on p.ProductID=o.ProductID
where p.productid like ' < Search for Monitors AND Sensors >'
OR o.manufacturer like '< Search for Monitors AND Sensors >'
......
I would like to use the LIKE operator instead of IN operator. I also cannot use Full Text search at this moment.
So Any idea how I can writethe SQL to do this?
I would be grateful if you can put me in the right direction.
Kind regards
-
select * from Products p
inner join OtherInfo o
on p.ProductID = o.ProductID
where p.productid like '%' + @your_keyword + '%'
or o.manufacturer like '%' + @your_keyword + '%'
-
Thanks KH
But the problem is that I will not know how many keywords there will be. A user may enter 'monitors' or 'monitors sensors' or 'monitors sensors LCD'
So I would have to search for each search keyword within my columns
regards
-
One method may be to use REPLACE and replace each space with the '%' wildcard.
create table #x ( word varchar(40) not null )
insert #x select 'monitors'
insert #x select 'sensors'
insert #x select 'LCD'
insert #x select 'monitors and sensors'
insert #x select 'monitors, sensors on LCD screens'
declare @searchphrase varchar(255)
set @searchphrase = 'monitors sensors LCD'
set @searchphrase = replace(@searchphrase, ' ', '%')
select * from #x where word like '%' + @searchphrase + '%'
word
----------------------------------------
monitors, sensors on LCD screens
(1 row(s) affected)
drop table #x
go
/Kenneth
-
Kenneth, I just tried it, but I get errors:
Incorrect syntax near the keyword 'LIKE'.
-
You may want to use dynamic SQL. I don't think this solution is elegant.
SET NOCOUNT ON
GO
CREATE TABLE #MyTable
(
myData VARCHAR(100)
)
INSERT INTO #MyTable
SELECT 'Basically' UNION
SELECT 'I' UNION
SELECT 'want' UNION
SELECT 'to' UNION
SELECT 'write' UNION
SELECT 'a' UNION
SELECT 'search' UNION
SELECT 'query' UNION
SELECT 'that' UNION
SELECT 'will' UNION
SELECT 'search' UNION
SELECT 'in' UNION
SELECT 'my' UNION
SELECT 'product' UNION
SELECT 'table.' UNION
SELECT 'I' UNION
SELECT 'want' UNION
SELECT 'to' UNION
SELECT 'search' UNION
SELECT 'for' UNION
SELECT 'ALL' UNION
SELECT 'the' UNION
SELECT 'search' UNION
SELECT 'keywords' UNION
SELECT 'within' UNION
SELECT 'the' UNION
SELECT 'table' UNION
SELECT 'columns.'
/* Query */
DECLARE @mySearchString VARCHAR(100)
SET @mySearchString = 'sea tab bas'
DECLARE @sql VARCHAR(2000)
SELECT @sql = 'SELECT * FROM #MyTable WHERE myData LIKE ''%' + REPLACE(@mySearchString, ' ', '%'' OR myData LIKE ''%') + '%'''
EXEC(@SQL)
GO
DROP TABLE #MyTable
Regards,
gova -
Well, try again.
I just copied the post and pasted into QA, and it ran with no problems. You realize that it's demonstrational, you must adapt it to your own tables.
/Kenneth
-
You may not want to use dynamic SQL for this purpose. There's absolutely no reason at all to use dynamic SQL in this case! Dynamic SQL is NOT the answer to every and all things
/Kenneth
-
My Mistake.
I thought any of the words. Not all the words.
I still go with dynamic SQL with AND instead of OR because following search wouldn't return any rows. The words need to be in order for solution without dynamic.
create table #x ( word varchar(40) not null )
insert #x select 'monitors'
insert #x select 'sensors'
insert #x select 'LCD'
insert #x select 'monitors and sensors'
insert #x select 'monitors, sensors on LCD screens'
declare @searchphrase varchar(255)
set @searchphrase = 'sensors LCD monitors'
set @searchphrase = replace(@searchphrase, ' ', '%')
select * from #x where word like '%' + @searchphrase + '%'
drop table #x
go
Regards,
gova -
Agreed, postions is a problem - didn't think of that. However, that should be solvable wihtout resorting to dynamic SQL nevertheless. You should be able write a function for that purpose without using dynamic SQL.
/Kenneth
-
Thanks Kenneth
I have copied the code as you provided it, and yes it works.
I will be adopting the code to my Tables
Just another question:
How does replacing a space with %, make it work?
The query will become..."where word like '%monitors%sensors%LCD%'
????
regards
-
Unfortunately
where word like '%monitors%sensors%LCD%'
and
where word like '%sensors%monitors%LCD%'
will return different results.
If to modify govinn's prescription:
create table #x ( word varchar(40) not null )
insert #x select 'monitors'
insert #x select 'sensors'
insert #x select 'LCD'
-- table #X contains list of keywords. You may need SP or UDF to convert comma separated list of words to temp table.
-- If you search this forum you'll find out dosen of solutions for this.
SELECT SearchColumn
FROM SearchTable
INNER JOIN #X on SearchColumn LIKE '%' + Word + '%'
GROUP BY SearchColumn
HAVING COUNT(SearchColumn) = (Select Count(Word) from #X)
_____________
Code for TallyGenerator -
Yes, as discovered, if the user enters mutliple words, the words has to be in the same order in the text row searched as the user has entered them.
On the other hand, let's not forget that this problem is best solved by full text search. Even though that was not available for some reason, if the 'workaround' we all try to find proves to be too involved, perhaps a new evaluation as to the possibility to implement full text services anyway would be the best solution.
/Kenneth
-
Why not Use PatIndex And test for the existence of the pattern anywhere in the string?
Select CategoryId, Description
from Categories
Where
Patindex('%wee%', Description) <> 0
And Patindex('%es%', Description) <> 0
Use this against the Northwinds db. It'll find two records with "wee" and "es" in them, in different positions.
I am not sure what this will do for performance on a large table. I tested it on one of my tables with just over 3k rows, the column being search is not indexed and it took .01 seconds to return 11 rows based on my criteria.
Enjoy.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply