How do I do a like '%<string>%' equivalent in FTS

How do the following equivalent query in FTS.

select brandName from Brand b where b.brandName like '%lf%'

returns "Alfa Brand"

select brandName from Brand b where CONTAINS (b.brandName, '"*lf*"')
returns Zero rows

pls help, I just can't FTS to return the row.

thx
jt

I can replicate this error. Your syntax is correct but it looks like the fulltext engine is ignoring the first wildcard (*)

Therefore your query turns into

select brandName from Brand b where CONTAINS (b.brandName, '"lf*"')

I'm not sure if this is standard behaviour across all installs of SQL. I'll see if i can find out.

|||I don't think FTS supports inter-word searching
|||There are Specific rules you will have to go with if you use FTS, depening on the wordbreaker you can use * at the beginning of a words. This depends on the wordbreaker which is used for the column / attribute.

e.g. if you search for *race in columns which contain TRACE and FASTRACE, you will find the second word as it was broken into FAST & RACE.

Jens K. Suessmeyer.

http://www.sqlserver2005.de