 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / April 2006Fullttext question
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.  SignatureHilary 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 > Hi there, > [quoted text clipped - 25 lines] > > Andy 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: > thx for your reply, > [quoted text clipped - 26 lines] > thx for your help > Andy 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 Dan, thx - that sounds good - will try that Andy Andreas wrote on 19 Apr 2006 02:31:00 -0700: > Dan, thx - that sounds good - will try that You might want to test the performance, and compare various ways of doing the sort. Dan the good thing is that the result table will always only 20 entries so i hope i dont run into performance problems andy 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 SignatureHilary 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 > thx for your reply, > [quoted text clipped - 26 lines] > thx for your help > 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. SignatureHilary 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 > what do you think, is faster, the solution of Dan or Hilary ? Andreas wrote on 19 Apr 2006 10:17:55 -0700: > what do you think, is faster, the solution of Dan or Hilary ? 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! SignatureHilary 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 > Andreas wrote on 19 Apr 2006 10:17:55 -0700: > [quoted text clipped - 5 lines] > > Dan Hilary wrote on Thu, 20 Apr 2006 06:28:01 -0400: > Daniel, I am not sure who has more experience - you are someone who > consistently impresses me! 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.