CONTAINS (Transact-SQL)

Question

Post reply

CONTAINS (Transact-SQL)

Dan Cook-293387

SSC Enthusiast

Points: 101
More actions
March 15, 2006 at 5:42 pm

#152831

Hi,
I'm trying to use Full Text Searching through SQL 2005.
I run coldfusion, and on my page a simple SQL statement I've used is
SELECT id, title, released, category, rrp, thumbnailurl, minimumprice
FROM tbl_dvds
WHERE CONTAINS (title, ' #URL.searchtitle# ')
ORDER BY released DESC
I don't fully understand the ways in which I can use this, but I know what I want out of it.
It works fine for single word searches, but throws an error for multiple words in it's current format.
IF i try
Contains(title, "Tom" OR "Red") I get results showing with either Tom, Red or Tom and Red in.
The actual results which contain BOTH words appear further down, and are not influenced particularly due to my ORDER BY.

How should my SQL read if I want it to display results with
TOM and RED in first, then TOM or RED however it likes?
Do I have to split up my search string, as is, using coldfusion? Or can the SQL handle it if I format the query correctly?

Thanks, Dan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vee Hall of Fame Points: 3028 More actions · Answer 1

I think you have to use rank clause in your query for more help see books on line for the keyword 'RANK'

EIJ Right there with Babe Points: 734 More actions · Answer 2

Dan,

Yes you need to use a RANK clause and can I suggest maybe using a NEAR boolean opperator instead of OR or AND as it will bring back results with a higher concentration at the top of your results set rather than at the bottom. Thus cattering for all eventualities.

Try this query: (or something very close)

SELECT Res.RANK AS RANK, id, title, released, category, rrp, thumbnailurl, minimumprice

FROM dbo.tbl_dvds INNER JOIN

CONTAINSTABLE(YOURFULLTEXTCATELOGUENAME, *, ' #URL.searchtitle# ' )

Res ON tbl_dvds.ID = Res.

ORDER BY Res.RANK DESC, tbl_dvds.released DESC

Your going to have to adapt it to use your fulltext catelogue name but I think the essence is there. your URL.searchtitle should be something like

"Tom" NEAR "Red" - Note, its important not to have any spaces between the double quotes, each word has to have a boolean opperator as a space.

Hope this helps.

Ed

P.s. Also, see the examples in this article:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_3syt.asp?frame=true

Dan Cook-293387 SSC Enthusiast Points: 101 More actions · Answer 3

Thanks for responses,

Ed, your query worked almost perfectly for me... though I did ammend it slightly.

SELECT Res.RANK AS RANK, primary_id, id, title, released, category, rrp, thumbnailurl, minimumprice

FROM dbo.tbl_dvds INNER JOIN

CONTAINSTABLE(dbo.tbl_dvds, *, '"#REReplaceNoCase(URL.searchtitle, ' ', '" OR "', 'ALL')#"')

Res ON tbl_dvds.primary_id = Res.

WHERE minimumprice IS NOT NULL

ORDER BY Res.RANK DESC, tbl_dvds.released DESC

I used OR again as NEAR cut down the results too much, and was similar to AND.

Putting in my FullCatalogueName ie. DVDSearch didn't seem to work. I put the table name in and it does.

http://www.silversurferuk.com/search_dvd.cfm?searchtitle=red+dwarf

It saw Red Red equal to Red Dwarf ... and even though NEAR / AND would give better results on this particular query - I'm happy with the current results.

Thanks,

I've always been one to bodge my code a bit if i can't work out the most efficient way So please tell me if I've gone wrong.

EIJ Right there with Babe Points: 734 More actions · Answer 4

Glad to have helped.

That query looks fine to me, if it works, it works.

I generally tend to use NEAR as with bigger data sets if cuts the wheat from the chaff but on smaller sets it shoudl be fine to use AND.