asked on

SQL Server 2000, Full Text

Hi,

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.

SELECT c.*
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

-ws

aungthiha

you can use the following way

SELECT c.*
FROM company c
WHERE companyName LIKE ' S%'

Regards

ASKER CERTIFIED SOLUTION

QPR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imran_fast

>>The docs say this should work I believe.

Hi,
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*" ')

Westside2004

ASKER

Hi,

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?

-ws

QPR

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?