Free Text Query

Question

Post reply

Free Text Query

Amit Lohia

SSCertifiable

Points: 6104
More actions
May 1, 2006 at 12:25 pm

#168114

If I have a column Notes varchar(100) which has values "Sacramento First Office"
I can search for that row with value of "men" by stating where notes like '%men%'
How can I get the same result by using Contains of Free text query.

Kindest Regards,
Amit Lohia

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

Amit,

I'm assumung you've already created a full text catelogue for this table?

If you have you just need to run a query like this one:

SELECT ColumnName

FROM ColumnName

WHERE CONTAINS(FulltextCatelogueName, 'men')

Hope this helps

Ed

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 2

Nope it does not work as men in part of the word and not the whole word or the start of the word

Kindest Regards,

Amit Lohia

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

No problem,

Try putting a wild card in so it becomes:

SELECT ColumnName

FROM ColumnName

WHERE CONTAINS(FulltextCatelogueName, '%men%')

Hope that helps.

Ed

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 4

Already done and no help

The actual wild character is "*" and not "%" and even the search should be part of double quote (")

FROM ColumnName

WHERE CONTAINS(FulltextCatelogueName, '"men*"')

Kindest Regards,

Amit Lohia

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

Sorry, just trying to help, i have it all coded somewhere but was working from memory to give you pointers. I would have looked it up for you if you had come back with problems.

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 6

Thank you EIJ for taking the time and atleast responseding. It is better to post any solution which you can think as many time we forget the most basic answer. Once again thank you.

Kindest Regards,

Amit Lohia

lxz20 Mr or Mrs. 500 Points: 592 More actions · Answer 7

Hi, Amit,

I have been wondering about the same issue with a table that has over 10 million rows. A Full-Text Index was built to facilitate free text search on one varchar(50) column. How surprised was I to find out that the CONTAINS query did not return anything if the search text was not a whole word or at the beginning of a word. It seems that LIKE '%Text%' is the only way to go. But it is so horribly slow. Did you get any solution?

Larry

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 8

Well I still do not have an answer infact, now this scenario is working as an advantage for us

Kindest Regards,

Amit Lohia

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

LXZ20 and Amit,

Are you saying this doesn't work?

FROM ColumnName

WHERE CONTAINS(FulltextCatelogueName, '"*men*"')

This is my code exactly I've just taken from a project which is verified to work:

SELECT DISTINCT Res.RANK AS RANK, ArticleID

FROM ARTICLESFT INNER JOIN

CONTAINSTABLE(ArticlesFT, *, '''*Men*''' )

Res ON ARTICLESFT.ID = Res.

Order by RANK DESC

Making it a little more generic it becomes:

SELECT DISTINCT Res.RANK AS RANK, COLUMNNAME

FROM TABLENAME INNER JOIN

CONTAINSTABLE(COLUMNNAME, *, '''*Men*''' )

Res ON TABLENAME.ID = Res.

Order by RANK DESC

I hope this helps, I just tried it and it returned results on my system, just note, there are no double quotes, just three single quotes before and after the search word.

lxz20 Mr or Mrs. 500 Points: 592 More actions · Answer 10

EIJ,

No, the following syntax didn't work:

FROM ColumnName

WHERE CONTAINS(FulltextCatelogueName, '"*men*"')

I tried the following, and it didn't return expected result, either.

FROM TableName

WHERE CONTAINS(ColumnName, '"*men*"')

Thanks.

Larry

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

What does it bring back?

A syntax error or just no results?