Solved

Keywords Search, search all words

Posted on 2010-09-02
14
540 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:markterry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33590221
you can use where condition like [assume @someText is declare variable that has text like "some text"]

Where KeyWord like '%' + REPLACE(@someText,' ','%') + '%'
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33590228
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 
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33590373
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)

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 6

Author Comment

by:markterry
ID: 33590816
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.
0
 
LVL 6

Author Comment

by:markterry
ID: 33590835
Your second solution is quite creative, however i can only edit the where clause.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33591240
not necessarily you can edit whatever you want there. I just included the relevant part based on your initial requirement.
0
 
LVL 6

Author Comment

by:markterry
ID: 33597992
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.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33598017
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)

Open in new window

0
 
LVL 6

Author Comment

by:markterry
ID: 33601243
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.

0
 
LVL 6

Author Comment

by:markterry
ID: 33601251
in the query  for example

select name from people
where name = 'me'

I can only change "where name ='me'"
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33601411
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.
0
 
LVL 6

Author Comment

by:markterry
ID: 33621162
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?
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33621387
then you will have to build your where clause in a programming language, (VB maybe?) to pass it to the CMS. I'm not familiar with Dotnuke programming so I cannot help you there. But, basically, you will have to convert the code I've suggested above to VB (or whatever language you're using), so the end result will be
"WHERE col1 like '%some%' and col1 like '%text%' "
(This is not dynamic SQL, this is the end result, that is the where clause you're looking for).
0
 
LVL 6

Author Closing Comment

by:markterry
ID: 33622465
Thanks, I guess I will have to give that a go.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question