Solved

SQL Server 2000, Full Text

Posted on 2007-03-27
5
252 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

615 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