Solved

SQL Server 2000, Full Text

Posted on 2007-03-27
5
245 Views
Last Modified: 2013-11-07
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

0
Comment
Question by:Westside2004
5 Comments
 
LVL 1

Expert Comment

by:aungthiha
ID: 18805714
you can use the following way


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

Regards

0
 
LVL 29

Accepted Solution

by:
QPR earned 500 total points
ID: 18805803
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...

CREATE PROCEDURE dbo.SearchFAQs
@strQuery varchar(1000),
as
declare @var varchar(1000)
set @var = ' FORMSOF (INFLECTIONAL, ' + @strQuery + ')'
select faqid, question
from FAQs
where contains(keywords,@var)
GO
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18806147
>>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*"  ')
0
 
LVL 1

Author Comment

by:Westside2004
ID: 18809140
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
0
 
LVL 29

Expert Comment

by:QPR
ID: 18811502
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?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now