 |
|
|
|
Next: help with the query
|
| Author |
Message |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 1) Posted: Thu Jun 05, 2008 10:21 am
Post subject: A question about keyword search strategy against SQL Server 2000
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
We have a small web application using Sql Server 2000.
We would like to provide a keyword search functionality. The key word
can be a location, a string in the description of certain topic or a
combination of both.
What I have currently is to take the keyword string as a single entity
and search the database using *like* like so:
WHERE my_locations_table.city like '%the given key word%' or
my_locations_table.state like '%the given key word%' or
my_topics_table.name like '%the given key word%' or
my_topics_table.description like '%the given key word%'
You see my idea.
Now, suppose the user enters "atlanta new york los angeles global
warming" in the search box with an intention to get all topics somehow
related to any of these given locations/topics in the keyword string.
My strategy will almost certainly return 0 records.
I know that we can split the given key word string, and create a
*like* for each of them like what I have. I think this is do-able,
but very cumbersome and maybe very low performance.
So, I am wondering if you gurus can share some of your smart tips with
regard to this search issue. Thank you. |
|
| Back to top |
|
 | |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 2) Posted: Thu Jun 05, 2008 11:33 am
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Jun 5, 1:36 pm, "Plamen Ratchev" wrote:
> You may want to look at implementing full text search:http://www.sqljunkies.ddj.com/HowTo/E823E84B-C443-4483-B4A3-CEF80EB34...http://support.microsoft.com/default.aspx?scid=kb;en-us;323739
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
OK, thank you very much. I was thinking if it is time for me to look
into full text search (FTS hereafter), but just wasn't sure if FTS is
the redirect direction for my problem since I don't know much about
it. |
|
| Back to top |
|
| |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 3) Posted: Thu Jun 05, 2008 11:35 am
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Jun 5, 1:57 pm, "Aaron Bertrand [SQL Server MVP]"
wrote:
> > Now, suppose the user enters "atlanta new york los angeles global
> > warming"
>
> Well, if you wanted to find strings that contain "new york" but not strings
> that contain "warming new", then the problem is much more complex. With the
> above it is easy to find *any* or *all* words. But if there are certain
> sequences expected within, there must be some kind of formatting on the
> input that says new is related to york...
>
> A
Yes, I am aware of this problem, and intentionally introduced such
locations as new york and los angeles. I am not ready to tackle this
problem yet, and are more concerned about the sql part at this point
of time. >> Stay informed about: A question about keyword search strategy against SQL Serve.. |
|
| Back to top |
|
| |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 4) Posted: Thu Jun 05, 2008 11:40 am
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Jun 5, 1:36 pm, "Plamen Ratchev" wrote:
> You may want to look at implementing full text search:http://www.sqljunkies.ddj.com/HowTo/E823E84B-C443-4483-B4A3-CEF80EB34...http://support.microsoft.com/default.aspx?scid=kb;en-us;323739
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
OK, thank you very much. I was thinking if it is time for me to look
into full text search (FTS hereafter), but just wasn't sure if FTS is
the right direction for my problem since I don't know much about it. >> Stay informed about: A question about keyword search strategy against SQL Serve.. |
|
| Back to top |
|
| |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 5) Posted: Thu Jun 05, 2008 11:50 am
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 6) Posted: Thu Jun 05, 2008 12:10 pm
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Jun 5, 3:02 pm, "Aaron Bertrand [SQL Server MVP]"
wrote:
> > Thank you for the references, I'll definitely look closely at both.
>
> > I use C#. The web application runs on .NET 2.0
>
> Note that you should build parameterized queries (and/or build the query
> inside a stored procedure) to protect yourself from SQL injection. A lot of
> these articles were written years before this became such a spreading
> problem.
Right, right, I usually do stored procedures only. >> Stay informed about: A question about keyword search strategy against SQL Serve.. |
|
| Back to top |
|
| |
External
Since: Jan 10, 2008
Posts: 1007
|
(Msg. 7) Posted: Thu Jun 05, 2008 1:36 pm
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
|
Aaron Bertrand [SQL Serve
|
External
Since: Jan 10, 2008
Posts: 2166
|
(Msg. 8) Posted: Thu Jun 05, 2008 1:57 pm
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
> Now, suppose the user enters "atlanta new york los angeles global
> warming"
Well, if you wanted to find strings that contain "new york" but not strings
that contain "warming new", then the problem is much more complex. With the
above it is easy to find *any* or *all* words. But if there are certain
sequences expected within, there must be some kind of formatting on the
input that says new is related to york...
A >> Stay informed about: A question about keyword search strategy against SQL Serve.. |
|
| Back to top |
|
| |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 9) Posted: Thu Jun 05, 2008 2:12 pm
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Jun 5, 1:36 pm, "Plamen Ratchev" wrote:
> You may want to look at implementing full text search:http://www.sqljunkies.ddj.com/HowTo/E823E84B-C443-4483-B4A3-CEF80EB34...http://support.microsoft.com/default.aspx?scid=kb;en-us;323739
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
I am trying to create full-text-search by following sqljunkies'
article (the first link you offered).
Question: Can we not specify more than one column of a table for full-
text indexing using sp_fulltext_column?
When I attempt to run something like
sp_fulltext_column 'my_table', 'field1', 'add'
sp_fulltext_column 'my_table', 'field2', 'add'
I get this error:
Incorrect syntax near 'sp_fulltext_column'.
No error if I run them one by one. But then does the last one
overwrite the previous one? Thank you. >> Stay informed about: A question about keyword search strategy against SQL Serve.. |
|
| Back to top |
|
| |
|
Aaron Bertrand [SQL Serve
|
External
Since: Jan 10, 2008
Posts: 2166
|
(Msg. 10) Posted: Thu Jun 05, 2008 2:44 pm
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
OK, with this problem aside, and barring FTS, you can look at this article:
http://databases.aspfaq.com/database/how-do-i-build-a-query-with-optio...-parame
Also see:
http://databases.aspfaq.com/database/how-do-i-ignore-common-words-in-a...arch.ht
Don't know what your client-side programming language is, but surely it
should be easy to port this code to any language such as C# or VB.Net...
"Author" wrote in message
> On Jun 5, 1:57 pm, "Aaron Bertrand [SQL Server MVP]"
> wrote:
>> > Now, suppose the user enters "atlanta new york los angeles global
>> > warming"
>>
>> Well, if you wanted to find strings that contain "new york" but not
>> strings
>> that contain "warming new", then the problem is much more complex. With
>> the
>> above it is easy to find *any* or *all* words. But if there are certain
>> sequences expected within, there must be some kind of formatting on the
>> input that says new is related to york...
>>
>> A
>
> Yes, I am aware of this problem, and intentionally introduced such
> locations as new york and los angeles. I am not ready to tackle this
> problem yet, and are more concerned about the sql part at this point
> of time. >> Stay informed about: A question about keyword search strategy against SQL Serve.. |
|
| Back to top |
|
| |
|
Aaron Bertrand [SQL Serve
|
External
Since: Jan 10, 2008
Posts: 2166
|
(Msg. 11) Posted: Thu Jun 05, 2008 3:02 pm
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
> Thank you for the references, I'll definitely look closely at both.
>
> I use C#. The web application runs on .NET 2.0
Note that you should build parameterized queries (and/or build the query
inside a stored procedure) to protect yourself from SQL injection. A lot of
these articles were written years before this became such a spreading
problem. >> Stay informed about: A question about keyword search strategy against SQL Serve.. |
|
| Back to top |
|
| |
External
Since: Jan 10, 2008
Posts: 1007
|
(Msg. 12) Posted: Thu Jun 05, 2008 5:30 pm
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 13) Posted: Fri Jun 06, 2008 5:51 am
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Jun 5, 5:30 pm, "Plamen Ratchev" wrote:
> When executing stored procedures you have to use the EXEC (EXECUTE) keyword.
> Only the first stored procedure in the batch can be executed without it.
>
> Try this:
>
> EXEC sp_fulltext_column 'my_table', 'field1', 'add'
> EXEC sp_fulltext_column 'my_table', 'field2', 'add'
>
> The procedure does not override columns in the index, it is just adding
> columns to the index.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
Thank you very much, Mr. Plamen. That helped. >> Stay informed about: A question about keyword search strategy against SQL Serve.. |
|
| Back to top |
|
| |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 14) Posted: Fri Jun 06, 2008 1:36 pm
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Jun 6, 8:51 am, Author wrote:
> On Jun 5, 5:30 pm, "Plamen Ratchev" wrote:
>
> > When executing stored procedures you have to use the EXEC (EXECUTE) keyword.
> > Only the first stored procedure in the batch can be executed without it.
>
> > Try this:
>
> > EXEC sp_fulltext_column 'my_table', 'field1', 'add'
> > EXEC sp_fulltext_column 'my_table', 'field2', 'add'
>
> > The procedure does not override columns in the index, it is just adding
> > columns to the index.
>
> > HTH,
>
> > Plamen Ratchevhttp://www.SQLStudio.com
>
> Thank you very much, Mr. Plamen. That helped.
I have successfully created full-text-search catalog for my database
and have also registered my tables, columns with the catalog.
I have run queries like the following just fine.
select * from my_table where contains(*, 'formsof(inflectional,
forum)')
select * from my_table where FREETEXT (*,'forum')
But, I still don't quite get how I am gonna write the query when a
user searches for "atlana denver global warming". Should I simply
split the search string and feed each of the 4 elements(atlanta,
denver, global, warming) to the following select query?
select * from my_table where contains(*, 'formsof(inflectional,
_____)')
Sorry, my first experience with full-text-search against SQL Server
2000. Any hint would be appreciated. >> Stay informed about: A question about keyword search strategy against SQL Serve.. |
|
| Back to top |
|
| |
External
Since: Jun 05, 2008
Posts: 42
|
(Msg. 15) Posted: Fri Jun 06, 2008 3:12 pm
Post subject: Re: A question about keyword search strategy against SQL Server 2000 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
| Related Topics: | Full Text Search / WHERE clause speed question - I need to design an app that does a full text search on 2 or 3 nvarchar(4000) size columns. But I only want to search records that are active, [IsActive] is true. Only 10% or so of the records will be active at any time. I'm wondering as far as..
Best indexing strategy - What indexing strategy is more likely to appease the sqlserver gods with respect to the queries below? My testing shows that a clustered index on sequence, sent flag and a non-clustered index on sequence performs pretty much the same as clustered..
Once again its on...(Query Strategy) - I find myself at the mercy of a poorly performing query. I am also providing the code which is actually used to build the two views. I do not list the DDL below since I am interested in strategies related to increasing the performance of the underlying..
"general" backup strategy - I am curious about general DBA's backup strategy. Do you backup directly to the network drive? or backup directly to the same server that hosts sql server and have window or other 3rd party software backup to a network drive or tape? When you do..
Strategy for INSERT query - I have two tables, tblLanguageCodes and tblLanguageValues: CREATE TABLE [dbo].[tblLanguageCodes]( [ID] [int] IDENTITY(1,1) NOT NULL, [LanguageCode] [nvarchar](10) NOT NULL, [LanguageDescription] [nvarchar](255) NOT NULL, [Active] [bit] NOT NULL.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
