sql server full text search: Hi there,

I have a database with ca. 150000 city names in the field city
(nvarchar).
When I now search like this:

....where CONTAINS(city,' "wash*" ')

I get a list of all results where the word "wash*" is included - thats
fine but not what I am looking for. Because the result looks like this

Camp Washington, Hamilton, Ohio, USA
East Washington, Sullivan, New Hampshire, USA
East Washington, Washington, Pennsylvania, USA
Fort Washakie, Fremont, Wyoming, USA
Fort Washington, Prince George's, Maryland, USA
and so on...

Is it possible that Cities that starts with "Wash..." are at the
beginning of the returned table?
Or did I need to manage this at the code-side (here ASP)
I know that i can do that with ... LIKE 'wash%' - but than I can't use
the fulltext features.

You see, I am a bit new to fulltext stuff - using SQL Server 2005

Thanks for any tipp or hint

Andy

I am a little confused, or maybe a lottle confused. Are you complaining that
you get hits to rows which contain the suffix wash anywhere in the full-text
indexed column. If so you should use a like like this - select * from city
where column like 'wash%'. SQL Full-Text search is insensitive to where the
match occurs. It could be the first word, the last word, or a word
in-between.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

thx for your reply,

to go in detail:
I have 1 column in which I have stored ca. 150000 city names
the column is defined as nvarchar and I have a fulltextindex on this
field
Because: as I know fulltext is faster than the normal LIKE without a
fulltext - or is LIKE using also the fulltext index?? As I know this
only happen when you use CONTAINS and FREETEXT - or am I totaly wrong
on this??

When I search for "wash*" i got all cities with "wash*" inside - thats
very ok - but I need to sort my results so that the user see results
first with words starting with "wash..." like the list below:

Washington, Pennsylvania, USA
Washington Square, New York, USA
Camp Washington, Hamilton, Ohio, USA
East Washington, Sullivan, New Hampshire, USA
East Washington, Washington, Pennsylvania, USA
Fort Washakie, Fremont, Wyoming, USA
Fort Washington, Prince George's, Maryland, USA

do you know if this is possible with SQL Server?
I need to use fulltext, because when users will search for "Washakie"
(see list above) they also should get a search result. So I can't
search only for entries starting with "wash..."

thx for your help
Andy

Andreas wrote on 18 Apr 2006 22:59:01 -0700:

[quoted text, click to view]

Try something like this:

SELECT * FROM (SELECT * FROM Table WHERE CONTAINS(city,' "wash*" ')) as A
ORDER BY CASE WHEN city LIKE 'wash%' THEN 0 ELSE 1 END

The ORDER BY in the outer select only acts on the results from the inner
select, and will force all rows where city starts with wash to the beginning
of the results.

Dan

Andreas wrote on 19 Apr 2006 02:31:00 -0700:

[quoted text, click to view]

You might want to test the performance, and compare various ways of doing
the sort.

Dan

Ok - this will work for just Washington -

select * from city where contains(*,'"wash*"') order by city desc

but if you have Zebra Hill, Washington, it will be ranked first so, I would
try something like this

select * from city
where contains(*,'"wash*"')
order by case when charindex('Washington',city) =0 then 999 else
charindex('washington',city) end

or to generalize it

declare @searchterm varchar(200)
set @searchterm='washington'
select * from city
where contains(*,@searchterm)
order by case when charindex(@searchterm,city) =0
then 999 else charindex(@searchterm,city) end

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Dan, thx - that sounds good - will try that

Andy

the good thing is that the result table will always only 20 entries
so i hope i dont run into performance problems

andy

thx hilary, I will try it
andreas

what do you think, is faster, the solution of Dan or Hilary ?

I'm biased. But mine is. Does it do what you are looking for should be the
deciding factor.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Andreas wrote on 19 Apr 2006 10:17:55 -0700:

[quoted text, click to view]


Run both in query analyser with the execution plans displayed - that way you
should get a good idea which is best in your setup. I'd lean towards
Hilary's, as he has a lot more experience with SQL than I do ;)

Dan

Daniel, I am not sure who has more experience - you are someone who
consistently impresses me!

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Hilary wrote on Thu, 20 Apr 2006 06:28:01 -0400:

[quoted text, click to view]

Wow, thanks for the vote of confidence. I don't think I'll be able to do
much work today as my head won't fit through the doors to the server room :P

I have 12 years experience working for a mail order/e-commerce company
(Computer Manuals, http://www.compman.co.uk and it's related sites http://www.bookfellas.co.uk
and http://www.sprintbooks.co.uk ) developing and maintaining all the
systems we use (both web facing and internal), but my SQL experience is
still quite limited (started with 6.5 but was still running most of the
databases on the websites I run in Access 97). I'm constantly learning, and
every now and again I go back to code I wrote a few years ago and optimise
it using things I've learnt in the meantime. I know enough to keep the
systems running here, but whenever I need to learn something new these
newsgroups are a good starting point - and you've helped me out a lot in my
education in FTS and replication :)

I'm nowhere near writing a book like you have - although I did almost write
one for Wrox Press (the UK office was next door to where I work until they
were taken over by Wiley) on Win-CGI way way back, I've been a Win-CGI
programmer in VB using ORA/Visnetic WebSite as the web platform for 10 of
those years, and only recently moved those CGIs to Standard CGI when we
moved to IIS a few months ago - thankfully a simple matter of switching a VB
module and recompiling. When I look back at that code I cringe - luckily
only 2 applications remain, and with some luck, and the boss not dropping
critical projects on me at the last moment, I might be able to finally
rewrite them as ASP+COM applications. Then again, I've been saying that for
the past 3 years, and those projects keep turning up on my desk ....

Dan

ok, Dan
thx

andreas