FullText Search - SQL Server

[suityou01]

In summary then, CONTAINS(YourColumn,'Att') doesn't work but CHARINDEX('Att', Yourcolumn) >0 gets results?

You've created the full-text catalog?

You've created the full-text index on the table and correctly associated it with the catalog above?

Have you tried doing it by using the wizard in SSMS?

Your summary is perfectly correct.

I tried dropping the index and the catalog and re-creating using the GUI. Still no joy.

[LondonManc]

Your summary is perfectly correct.

I tried dropping the index and the catalog and re-creating using the GUI. Still no joy.

I won't have access to SQL Server until weekend, so I guess we'll have to keep an idiot in suspense to see if I can recreate it.

[teapot418]

Hello, can you help me as I am too stupid to figure this out for myself.

I want to enable FullText search on a Text column. I have added a FullText catalog, and added the column to the catalog and populated the index.
The FullText search service is running.

However I get no hits. I should point out that StopWords is off.

Soooo

If my column contains 'Attrn' and I search on CONTAINS (MyColumn,'Att') I would expect to get a hit, but I don't.

Are there any other bear traps I haven't considered?

FullText is doing a language aware search.

CONTAINS(MyColumn, 'Att') is looking for the word Att - in this case your word is Attrn so it's not finding a match

Try

CONTAINS (MyColumn, '"Att*"' )

[teapot418]

Att with CONTAINS or FREETEXT should turn up the row where a correctly indexed column contains Att (no wildcards needed).

Contains the word Att - I think that's where this is failing. It's not doing a string match, it's doing a word match - you need the wildcard for a string match.

[LondonManc]

FullText is doing a language aware search.

CONTAINS(MyColumn, 'Att') is looking for the word Att - in this case your word is Attrn so it's not finding a match

Try

CONTAINS (MyColumn, '"Att*"' )

Yep, I think this is the key - "Attrn" is not recognised as a logical linguistic extension of "Att". I finally cheated and RTFM

[MrMarkyMark]

Yep, I think this is the key - "Attrn" is not recognised as a logical linguistic extension of "Att". I finally cheated and RTFM

And you message to Suity is?

[LondonManc]

And you message to Suity is?

If he'd have RTFM'd yesterday, he wouldn't have need to post.

SQL Server BooksOnLine is an excellent resource in itself, without the countless blogs out there.

[original PM]

surely just

where columnname like '%textyouarelookingfor%'

works fine too?

or am I missing something?

[LondonManc]

surely just

where columnname like '%textyouarelookingfor%'

works fine too?

or am I missing something?

full text index offers better performance. Wildcards kill off the advantages that you get (a bit like adding a function to a join when joining a date to a datekey integer for example).

[suityou01]

You mean apart from the fact that code is wrong......

How do you get leading wildcard full-text searches to work in SQL Server? - Stack Overflow

My invoice is in the post

You lost me. How is my code wrong?

No Wildcard

You don't always need a wild card. I understand this would force a full table scan, which kind of negates the effect of going to all the trouble of creating a full text index. Contains should match on the string wherever your search string occurs.

Yep, I think this is the key - "Attrn" is not recognised as a logical linguistic extension of "Att". I finally cheated and RTFM

And you message to Suity is?

Contains the word Att - I think that's where this is failing. It's not doing a string match, it's doing a word match - you need the wildcard for a string match.

FullText is doing a language aware search.

CONTAINS(MyColumn, 'Att') is looking for the word Att - in this case your word is Attrn so it's not finding a match

Try

CONTAINS (MyColumn, '"Att*"' )