Solved

SQL Server 2000, Full Text

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

828 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