Solved

SQL Server 2000, Full Text

Posted on 2007-03-27
5
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Viewers will learn how the fundamental information of how to create a table.

752 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