SQL Server 2000, Full Text


I am using sql server 2000 and I have setup full text indexing on a few columns in this table.  I am using the following sql.

FROM company c
WHERE CONTAINS(companyName, ' "S*" ')

This query works, but for some reason (I think because I am using CONTAINS) I am getting a record back that does not start with S as the first word, its actually the 3rd work in this particular companyName and its coming back and in my resultset.

I get this in the resultset:   ABC IT Solutions, Inc.

I want it to only look at the first letter of the companyName.  I tried using Left() but to no avail.  It appears it sees the word Solutions  when I do s*.  The docs say this should work I believe.

Any help appreciated


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you can use the following way

FROM company c
WHERE companyName LIKE ' S%'


if only interested in "starts with s" then like (as above) will do the job... although you will have problems if comapnyname is a text data type.
If you want more of a search engine functionality then here is something i use...

@strQuery varchar(1000),
declare @var varchar(1000)
set @var = ' FORMSOF (INFLECTIONAL, ' + @strQuery + ')'
select faqid, question
from FAQs
where contains(keywords,@var)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
>>The docs say this should work I believe.

first Check that you have latest service pack on your sql server. if not apply that and then repopulate your catalog.

paste it exaclty.
WHERE CONTAINS(companyName, '  "S*"  ')
Westside2004Author Commented:

I would prefer not to use LIKE.  I enabled full-text search and did population so I would specifically not have to use LIKE.  I am aware of using that and while it does work, I am trying to utilize the db.

Also, QPR, how does using FORMSOF search from the first letter on?  It seems that just searchs words that are similar?

It does search for similar words, I was putting forward another suggestion.
You need to use LIKE to achieve what you want otherwise think outside the square... maybe have another column in the table that is the first letter of the comapny name and search on that
select * from company where FirstChar = 's'

utilize the db?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.