markterry
asked on
Keywords Search, search all words
Hello,
I am having trouble rapping my head around the logic of a keyword search. I would like to have a search string input into the procedure to find the keywords in a fieild. This is not too hard, but my problem is making is so that it matches all keywords seperated by spaces in any order. For example if I enter the keywords "Some text" it should find results that have a sentence with "some text" or "text some".
Anyone know how you would go about this?
I am having trouble rapping my head around the logic of a keyword search. I would like to have a search string input into the procedure to find the keywords in a fieild. This is not too hard, but my problem is making is so that it matches all keywords seperated by spaces in any order. For example if I enter the keywords "Some text" it should find results that have a sentence with "some text" or "text some".
Anyone know how you would go about this?
If you are trying to implement some sort of fuzzy search then you should use Full text index. check the below for details:
http://www.developer.com/db/article.php/3446891/Understanding-SQL-Server-Full-Text-Indexing.htm
http://www.developer.com/db/article.php/3446891/Understanding-SQL-Server-Full-Text-Indexing.htm
If you just want an exact match of all the keywords regardless of the order, then you can use dynamic SQL like below:
declare @strsQL varchar(8000)
declare @col varchar(500)
set @col = '%' + replace(@yourparam, ' ', '% AND Col1 Like %') + '%'
set @strSQL = 'select * from yourtable where col1 like ' + @col
exec(@strSQL)
ASKER
problem is I am going through a CMS that allows me to use SQL syntax for querying, but I can't use full-text because the data is serialized.
ASKER
Your second solution is quite creative, however i can only edit the where clause.
not necessarily you can edit whatever you want there. I just included the relevant part based on your initial requirement.
ASKER
No, what I am saying is, because of my CMS, i can only provide the where clause, so I need a solution that can be done with a creative where clause.
well, if you just want the WHERE clause then
declare @strsQL varchar(8000)
declare @col varchar(500)
set @col = '%' + replace(@yourparam, ' ', '% AND Col1 Like %') + '%'
set @strSQL = 'where col1 like ' + @col
exec(@strSQL)
ASKER
I can put a declare inside a where clause?
I still dont think you understand what I am saying
I can only change the WHERE clause of a query, and I cannot do anything else through the CMS.
I still dont think you understand what I am saying
I can only change the WHERE clause of a query, and I cannot do anything else through the CMS.
ASKER
in the query for example
select name from people
where name = 'me'
I can only change "where name ='me'"
select name from people
where name = 'me'
I can only change "where name ='me'"
And that's what the above will do.
declare @strsQL varchar(8000)
declare @col varchar(500)
set @col = '''%' + replace(@yourparam, ' ', '''% AND Col1 Like %''') + '%'''
set @strSQL = 'where col1 like ' + @col
That will give you something like
"WHERE col1 like '%some%' and col1 like '%text%' "
Isn't that what you want? IF not please post a clear example of what are you looking for.
declare @strsQL varchar(8000)
declare @col varchar(500)
set @col = '''%' + replace(@yourparam, ' ', '''% AND Col1 Like %''') + '%'''
set @strSQL = 'where col1 like ' + @col
That will give you something like
"WHERE col1 like '%some%' and col1 like '%text%' "
Isn't that what you want? IF not please post a clear example of what are you looking for.
ASKER
Ralmada, thank you for your persistence.
I am working with a CMS called DotNetNuke. I am using the Forms and List module, which allows me to put in a filter statement using SQL syntax which effectively is the Where clause of a SQL statement. I cannot put dynamic SQL into this where clause, it has to be sql syntax you could normally place in a where clause.
Is that clear?
I am working with a CMS called DotNetNuke. I am using the Forms and List module, which allows me to put in a filter statement using SQL syntax which effectively is the Where clause of a SQL statement. I cannot put dynamic SQL into this where clause, it has to be sql syntax you could normally place in a where clause.
Is that clear?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, I guess I will have to give that a go.
Where KeyWord like '%' + REPLACE(@someText,' ','%') + '%'