?
Solved

Full Text Search Query in MSSQL 2008 in ASP

Posted on 2010-01-06
15
Medium Priority
?
918 Views
Last Modified: 2012-05-08
Hi All,
I've got the following full text query:

Search string being entered:

 SearchWords  = "apple pear orange"

SELECT distinct P.Title, P.Description, P.Link, R.RANK FROM TWS4UProducts as P INNER JOIN CONTAINSTABLE (TWS4UProducts,(title,description),'(" & """" & SearchWords & """" & ")') as R ON P.ID1 = R.[KEY] ORDER BY Rank DESC;

How can the above query be re-written so that it works for:

1) Find any word               i.e. apple OR pear OR orange
2) Find all words                i.e. apple AND pear AND orange
3) Find exact phrase         i.e.  "apple pear orange"

Many thanks

JamWales
0
Comment
Question by:Jamie
  • 8
  • 3
  • 2
  • +1
15 Comments
 
LVL 11

Expert Comment

by:Sven
ID: 26197714
1) SELECT * FROM fulltexttable WHERE CONTAINS(*,'"apple*"') OR CONTAINS(*,'"pear*"') OR CONTAINS(*,'"orange*"')
2) SELECT * FROM fulltexttable WHERE CONTAINS(*,'"apple*" AND "pear*" AND "orange*"')
3) SELECT * FROM fulltexttable WHERE CONTAINS(*,'"apple pear orange"')
0
 
LVL 22

Expert Comment

by:neeraj523
ID: 26197980
hello

Try this

<%
SearchWords  = "apple pear orange"

arr = Split(SearchWords, " ")

For i=0 To UBound(arr)
      If arr(i) <> "" Then
            sql = sql & " OR title like '%" & arr(i) & "%' OR description like '%" & arr(i) & "%' "
      End if
next

sql = "SELECT distinct P.Title, P.Description, P.Link, R.RANK FROM TWS4UProducts as P INNER JOIN CONTAINSTABLE ON P.ID1 = R.[KEY] WHERE 1=1 " & sql & " ORDER BY Rank DESC"
Response.Write sql
%>
0
 

Author Comment

by:Jamie
ID: 26203281
Hi both, thank you for your replies.

DarthSonic - it does work, but if I use "contains" instead of "containable" don't I lose the ranking feature - which I need as I want to sort by Rank/Relevance?

neeraj523: - getting errors, plus should the where clause say WHERE 1=2 or it will always be true?

SELECT distinct P.Title, P.Description, P.Link, R.RANK FROM TWS4UProducts as P INNER JOIN CONTAINSTABLE ON P.ID1 = R.[KEY] WHERE 1=1 OR title like '%apple%' OR description like '%apple%' OR title like '%pear%' OR description like '%pear%' OR title like '%orange%' OR description like '%orange%' ORDER BY Rank DESC

Microsoft OLE DB Provider for SQL Server error '80040e21'
The requested properties cannot be supported
Line 125

Where line 125 = ShowItems.Open  

Regards

JamWales
 
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 22

Expert Comment

by:neeraj523
ID: 26207130
sorry.. it should be 1=2 and be returning false

can u post complete line 125 ??   what is ShowItems ? a recordset ?

0
 

Author Comment

by:Jamie
ID: 26207563
Hi neeraj523,

ShowItems.Open is the complete line 125, and ShowItems is as you say a recordset.
set ShowItems= Server.CreateObject("ADODB.Recordset")
ShowItems.ActiveConnection = Application("DbConn")

ShowItems.Source = RUNSQL
ShowItems.CursorType = 0
ShowItems.CursorLocation = 2
ShowItems.LockType = 3
ShowItems.Open
The above commands work fine when RUNSQL=
SELECT distinct P.Title, P.Description, P.Link, R.RANK FROM TWS4UProducts as P INNER JOIN CONTAINSTABLE (TWS4UProducts,(title,description),'(" & """" & SearchWords & """" & ")') as R ON P.ID1 = R.[KEY] ORDER BY Rank DESC;

 With SearchWords  = "apple pear orange"
 Regards

JamWales
0
 
LVL 11

Assisted Solution

by:Sven
Sven earned 420 total points
ID: 26207676
In SQL Server 2008 you could use RANK or ROW_NUMBER statement for ranking:

http://msdn.microsoft.com/en-us/library/ms176102.aspx
http://msdn.microsoft.com/en-us/library/ms186734.aspx

Should work ony my statement above.
0
 
LVL 22

Accepted Solution

by:
neeraj523 earned 540 total points
ID: 26207688
try this

SELECT distinct P.Title, P.Description, P.Link, R.RANK FROM TWS4UProducts as P INNER JOIN CONTAINSTABLE as R ON P.ID1 = R.[KEY] WHERE 1=1 OR title like '%apple%' OR description like '%apple%' OR title like '%pear%' OR description like '%pear%' OR title like '%orange%' OR description like '%orange%' ORDER BY Rank DESC

it worked at my end by creating same tables etc
0
 

Author Comment

by:Jamie
ID: 26211311
Hi both,

DarthSonic - Hmmmm, RANK or ROW_NUMBER I'll try it - but I'm not sure these are the same as in a full index search context?
 
neeraj523: - I know I shouldn't but I'm still getting the same error?! I know it should not make any difference, but what version of MSSQL are you using - I'm on MSSQL2008 64 bit, Web Edition.

SELECT distinct P.Title, P.Description, P.Link, R.RANK FROM TWS4UProducts as P INNER JOIN CONTAINSTABLE as R ON P.ID1 = R.[KEY] WHERE 1=1 OR title like '%apple%' OR description like '%apple%' OR title like '%pear%' OR description like '%pear%' OR title like '%orange%' OR description like '%orange%' ORDER BY Rank DESC

Microsoft OLE DB Provider for SQL Server error '80040e21'
The requested properties cannot be supported
Line 125

Where line 125 = ShowItems.Open  

Regards

JamWales
0
 

Author Comment

by:Jamie
ID: 26213217
Hi Both,

Not sure why your solutions did not work fully - I got the following to work:

SELECT P.ID1, P.Title, P.Description, P.Link, P.Category_ID, KEY_TBL.RANK FROM TWS4UProducts AS P INNER JOIN CONTAINSTABLE (TWS4UProducts,(title,description),'"%apple%" OR "%pear%" OR "%orange%"', 5) AS KEY_TBL ON P.ID1 = KEY_TBL.[KEY];

http://support.microsoft.com/kb/246800

Regards

JamWales

0
 

Author Comment

by:Jamie
ID: 26213549
Hi Both,
Very sorry, forgot to thank you for your help - it's not quite working yet!
Regards
JamWales
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26250791
This is not correct:
SELECT P.ID1, P.Title, P.Description, P.Link, P.Category_ID, KEY_TBL.RANK FROM TWS4UProducts AS P INNER JOIN CONTAINSTABLE (TWS4UProducts,(title,description),'"%apple%" OR "%pear%" OR "%orange%"', 5) AS KEY_TBL ON P.ID1 = KEY_TBL.[KEY];

It should be:
SELECT P.ID1, P.Title, P.Description, P.Link, P.Category_ID, KEY_TBL.RANK FROM TWS4UProducts AS P INNER JOIN CONTAINSTABLE (TWS4UProducts, (title,description), '"apple OR pear OR range"', 5) AS KEY_TBL ON P.ID1 = KEY_TBL.[KEY];


Also, what is the last parameter in the CONTAINSTABLE which has a value 5.  If you are intending that to be the Language than 5 does not appear to be correct, it is the LCID for the language.  Perhaps you thought it was the langid (langid = 5  --> Alias = Spanish, LCID= 3082)
0
 

Author Comment

by:Jamie
ID: 26264098
Hi acperkins,

My example did work, as yours did with OR, but neither worked if I want AND to find all words found? I also need to now how to only return rows containing the exact phrase.

SELECT P.ID1, P.Title, P.Description, P.Link, P.Category_ID, KEY_TBL.RANK FROM TWS4UProducts AS P INNER JOIN CONTAINSTABLE (TWS4UProducts, (title,description), '"apple AND pear AND orange"', 5) AS KEY_TBL ON P.ID1 = KEY_TBL.[KEY];

CONTAINSTABLE allows you rank your results and also to limit the number returned rows - hence the last paramter of 5 - which, in this example limits the number of returned rows to 5.

http://msdn.microsoft.com/en-us/library/ms189760.aspx

http://books.google.co.uk/books?id=wGwVkAt79bEC&pg=PA69&lpg=PA69&dq=full+text+search+containstable&source=bl&ots=fukDha9l5g&sig=Hw7L28iza66I4TdyV-KQ0eP7ktc&hl=en&ei=kpdHS7i_Co_-0gTs8vzmAQ&sa=X&oi=book_result&ct=result&resnum=10&ved=0CCoQ6AEwCQ#v=onepage&q=full%20text%20search%20containstable&f=false

I've got the above book on order, but it has not arrived as yet.
Regards
JamWales
0
 

Author Comment

by:Jamie
ID: 26272438
Hi All,
I think I've got it working. It seems the AND statement only works in each seperate field, so if you are looking for 2 words, but only 1 word appears in each field, the row is not returned. The solution is to seems to be to select a concatenated field: P.Title + P.Description - then it finds both words in the concatenated field.
Many thanks
JamWales
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 540 total points
ID: 26275822
>>CONTAINSTABLE allows you rank your results and also to limit the number returned rows - hence the last paramter of 5 - which, in this example limits the number of returned rows to 5.<<
This is the syntax for CONTAINSTABLE:
CONTAINSTABLE ( table , { column_name | (column_list ) | * } , ' < contains_search_condition > '
     [ , LANGUAGE language_term]
  [ , top_n_by_rank ]

As you can see the LANGUAGE parameter comes before the top_n_rank.

>>so if you are looking for 2 words, but only 1 word appears in each field, the row is not returned.<<
Of course.

>>The solution is to seems to be to select a concatenated field: P.Title + P.Description - then it finds both words in the concatenated field.<<
Make that a computed column and then create a Full-Text Catalog on that computed column.
0
 

Author Closing Comment

by:Jamie
ID: 31673749
Many thanks for all your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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