How to display records according to this rule?

[Miguel Dias Moura]

Hello,

I have a search form in an ASP.NET/VB page.
The form has the input text box and the button "search".

The keywords are passed in the URL to results.aspx.

Here is an example:
results.aspx?search=asp%20book%20london

(%20 means for space)

I used the words "asp", "book", "london".

Each database record has 2 fields: "title" and "description".

I want to display all the records where at least one of the keywords is
found in any of the 2 fields "title" and "description".

1. I suppose I need to get the words out of the URL to be used by SQL.
Am I right? How can I do this?

2. How should the SQL look?

I have been applying filters but never something as complex as this
search.

Thanks,
Miguel

 

[Kevin Spencer]

This isn't really complex at all.

1. I suppose I need to get the words out of the URL to be used by SQL.
Am I right? How can I do this?

string[] aryValues = Request.QueryString("search").Split(' ');

2. How should the SQL look?

SELECT * FROM mytable WHERE title LIKE '%asp%'
OR title LIKE '%book%'
OR title LIKE '%London%'
OR description LIKE '%asp%'
OR description LIKE '%book%'
OR description LIKE '%London%'

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Neither a follower
nor a lender be.

 

[Martin Dechev]

Hi,

If you enable Full-Text Search on the database and also the table in
question you can use the following query and pass directly the search
string:

SELECT < some columns or * > FROM < your table >
INNER JOIN
FREETEXTTABLE(< your table >, < column or * >, @p_freetext) AS KEY_TBL
ON < your table >.< your table's unique key> = KEY_TBL.[KEY]
(optionally
WHERE ....
(recommended
ORDER BY KEY_TBL.[RANK]

Then just add the @p_freetext parameter to the parameters of the command.

See the topics related to full-text querying in MS SQL Server Books Online
for more details and examples.

Hope this helps
Martin Dechev
ASP.NET MVP

 

[Miguel Dias Moura]

Hi,

I am a little bit about your solution.
Sorry but I am new in this.

How to I apply " string[] aryValues =
Request.QueryString("search").Split(' ');" to be executed when the page
loads?

The number of Keywords it's not always 3. It can be more.
I suppose your SQL doesn't predicts that.

Thanks,
Miguel

This isn't really complex at all.

1. I suppose I need to get the words out of the URL to be used by SQL.
Am I right? How can I do this?

string[] aryValues = Request.QueryString("search").Split(' ');

2. How should the SQL look?

SELECT * FROM mytable WHERE title LIKE '%asp%'
OR title LIKE '%book%'
OR title LIKE '%London%'
OR description LIKE '%asp%'
OR description LIKE '%book%'
OR description LIKE '%London%'

--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Neither a follower
nor a lender be.

Hello,

I have a search form in an ASP.NET/VB page.
The form has the input text box and the button "search".

The keywords are passed in the URL to results.aspx.

Here is an example:
results.aspx?search=asp%20book%20london

(%20 means for space)

I used the words "asp", "book", "london".

Each database record has 2 fields: "title" and "description".

I want to display all the records where at least one of the keywords
is
found in any of the 2 fields "title" and "description".

1. I suppose I need to get the words out of the URL to be used by SQL.
Am I right? How can I do this?

2. How should the SQL look?

I have been applying filters but never something as complex as this
search.

Thanks,
Miguel

 

[Miguel Dias Moura]

Hi,

It seems good but I got lost.

Where can I find MS SQL Server Books Online?

Is there any disadvantage of enabling a database to Full-Text Search?

Thanks,
Miguel

Hi,

If you enable Full-Text Search on the database and also the table in
question you can use the following query and pass directly the search
string:

SELECT < some columns or * > FROM < your table >
INNER JOIN
FREETEXTTABLE(< your table >, < column or * >, @p_freetext) AS KEY_TBL
ON < your table >.< your table's unique key> = KEY_TBL.[KEY]
(optionally
WHERE ....
(recommended
ORDER BY KEY_TBL.[RANK]

Then just add the @p_freetext parameter to the parameters of the
command.

See the topics related to full-text querying in MS SQL Server Books
Online
for more details and examples.

Hope this helps
Martin Dechev
ASP.NET MVP

Hello,

I have a search form in an ASP.NET/VB page.
The form has the input text box and the button "search".

The keywords are passed in the URL to results.aspx.

Here is an example:
results.aspx?search=asp%20book%20london

(%20 means for space)

I used the words "asp", "book", "london".

Each database record has 2 fields: "title" and "description".

I want to display all the records where at least one of the keywords
is
found in any of the 2 fields "title" and "description".

1. I suppose I need to get the words out of the URL to be used by SQL.
Am I right? How can I do this?

2. How should the SQL look?

I have been applying filters but never something as complex as this
search.

Thanks,
Miguel

 

[Martin Dechev]

Hi,

Hi,

It seems good but I got lost.

Where can I find MS SQL Server Books Online?

If have chosen to install them (they are optional components in the SQL
Server installation) - Start -> Programs -> Microsoft SQL Server -> Books
Online.

Is there any disadvantage of enabling a database to Full-Text Search?

The SQL Server process takes some more memory (and some CPU time when
creating and updating the index). For small databases - there is small
difference. Also, for small databases Kevin's solution is ok - it will be
reasonably fast.

Greetings
Martin Dechev
ASP.NET MVP

Thanks,
Miguel

Hi,

If you enable Full-Text Search on the database and also the table in
question you can use the following query and pass directly the search
string:

SELECT < some columns or * > FROM < your table >
INNER JOIN
FREETEXTTABLE(< your table >, < column or * >, @p_freetext) AS KEY_TBL
ON < your table >.< your table's unique key> = KEY_TBL.[KEY]
(optionally
WHERE ....
(recommended
ORDER BY KEY_TBL.[RANK]

Then just add the @p_freetext parameter to the parameters of the
command.

See the topics related to full-text querying in MS SQL Server Books
Online
for more details and examples.

Hope this helps
Martin Dechev
ASP.NET MVP

Hello,

I have a search form in an ASP.NET/VB page.
The form has the input text box and the button "search".

The keywords are passed in the URL to results.aspx.

Here is an example:
results.aspx?search=asp%20book%20london

(%20 means for space)

I used the words "asp", "book", "london".

Each database record has 2 fields: "title" and "description".

I want to display all the records where at least one of the keywords
is
found in any of the 2 fields "title" and "description".

1. I suppose I need to get the words out of the URL to be used by SQL.
Am I right? How can I do this?

2. How should the SQL look?

I have been applying filters but never something as complex as this
search.

Thanks,
Miguel