Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-20 : 07:13:17
|
| david writes "Have a front end where users can search the database for text entered into comment fields. ItExample:select *from tablenamewhere textfield like '%air%' Which would find, 'airbus', 'ryanair' 'air' etcWhen they enter 'air' and it default the '%' around the textThey also have the option of turning thios off and just searching for the whole word.If they entered 'air', would find just 'air' and not 'airbus' or 'ryanair'.To do this in a sentence seem complicated:select *from tablenamewhere (textfield like 'air %' or textfield like '% air %' or textfield like '% air')just using the phrase '% air %' doesn't find hits when the sentence starts or ends with 'air'. Is there an easier way?Full text indexing isn't an option at the moment, but maybe in the future.thanksdavid" |
|
|
RM
Yak Posting Veteran
65 Posts |
Posted - 2005-01-20 : 07:22:55
|
| I presume you are checking this through a sproc. You can easily manipulate this through the input parameter. Your sproc would become somewhat like thisCreate Proc SPName@searchString varchar(50)Asselect *from tablenamewhere textfield like @searchStringSo if you want to search 'air%', '%air%' or %air' pass this in the input parameter. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-20 : 07:53:54
|
| [code]select *from tablenamewhere ' ' + textfield + ' ' like '% air %'[/code]However:This won't work for a TEXT datatype (varchar is OK up to 8000-2 characters, nvarchar up to 4000-2 characters)Usingwhere ' ' + textfield + ' ' like ....means that no indexes can be used on "textfield" to speed up the query - a table scan will be used. Might not be a problem for you, but worth being aware of it.Kristen |
|
|
|
tylerdare
Starting Member
2 Posts |
Posted - 2006-02-23 : 10:41:39
|
| Kristen your saying that an index will not help when using a like search? I am a having a problem with a field that I have a full text index setup on. The Full text indexing is so slow it is useless. I was temped to build a regular index and try using like. Sounds as if it would do no good. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-23 : 10:46:38
|
| SELECT FROM MyTable WHERE MyColumn = 'FOO'andSELECT FROM MyTable WHERE MyColumn LIKE 'FOO%'should use an index.SELECT FROM MyTable WHERE MyColumn LIKE '%FOO%'is unlikely to.One option would be to "split" the words out of MyColumn into a table of "words" and search on that - should be quicker - assuming "words", rather than "partial words", is what you are after.We do this to find people by first/middle/last/former name. We also store a SOUNDEX column as well as the name "word", and search on that. That gives us a shortlist to JOIN back to the original table to search it more intelligently.I'm surprised you are finding Full Text slow ... that's not normally the case. It also has the benefit of knowing about root words and synonyms (I think) etc. which can be handy - but it depends on your application I suppose ...Kristen |
|
|
|
tylerdare
Starting Member
2 Posts |
Posted - 2006-02-23 : 10:58:28
|
| Kristen thanks for the info. The database stores the logs from a proxy server. The Field I am indexing is the URL that someone visited it is varchar(250). If the ip address is being watched for going to "bad sites" it can store 250 but if it is not it is trimed to 110. The records are purged at 30 days so a job executes at night that will purge them. If Full text index is running that job fails due to locks from the Full text indexing. The only way around it has been to stop the microsoft search service. I tried purging with a cursor but limited sucess. I'm not sure if that is causing problem but I started a full population Tuesday and it still has not finished. I am hoping going to 2005 will solve some of these issues. |
|
|
|
|
|
|
query - using like