Searching a Text column (SQL Server 6.5) 
Author Message
 Searching a Text column (SQL Server 6.5)

Hi all,

I've built a VB6 front end to a SQL Server 6.5 database using ADO. My
question is more SQL related but I'm guessing that some of you VB guys (or
gals) out there can help me.

I want to let the user search for keywords in a text column in the database.
Currently, I have a stored proc on the database that accepts a varchar
string as a parameter. The user can enter a search string in a text box on a
form, which is then sent to the stored procedure to perform the query. The
problem is the limited "intelligence" of the LIKE operator in SQL. What I'm
doing is first splitting the string on spaces and inserting '%' between each
word in the string.

For instance, let's call the text column 'notes' and the stored procedure
'SearchNotes'. The WHERE clause of the proc would look like:


like:
"%word%excel%"

This of course would find a row where the notes field contained:
"The user is having problems with word and excel."

However, it would not find:
"The user is having problems with excel and word." or
"The user is having problems with word."

How would you go about creating a "search engine" that will find any or all
of the keywords in the query, no matter what order they appear in the
database field? I guess this would be sort of like the search engines on web
sites. Can this be done with a VB/SQL Server application? Any ideas or tips
would be appreciated.

Thanks,
Kevin Kuebler



Fri, 21 Sep 2001 03:00:00 GMT  
 Searching a Text column (SQL Server 6.5)
Kevin,
I think you may want to parse you SQL statement a little farther and pass in
something to the effect of:
"%word% or notes like %excell%"
This would return either and both.

TW Scannell

Quote:

>Hi all,

>I've built a VB6 front end to a SQL Server 6.5 database using ADO. My
>question is more SQL related but I'm guessing that some of you VB guys (or
>gals) out there can help me.

>I want to let the user search for keywords in a text column in the
database.
>Currently, I have a stored proc on the database that accepts a varchar
>string as a parameter. The user can enter a search string in a text box on
a
>form, which is then sent to the stored procedure to perform the query. The
>problem is the limited "intelligence" of the LIKE operator in SQL. What I'm
>doing is first splitting the string on spaces and inserting '%' between
each
>word in the string.

>For instance, let's call the text column 'notes' and the stored procedure
>'SearchNotes'. The WHERE clause of the proc would look like:


>like:
>"%word%excel%"

>This of course would find a row where the notes field contained:
>"The user is having problems with word and excel."

>However, it would not find:
>"The user is having problems with excel and word." or
>"The user is having problems with word."

>How would you go about creating a "search engine" that will find any or all
>of the keywords in the query, no matter what order they appear in the
>database field? I guess this would be sort of like the search engines on
web
>sites. Can this be done with a VB/SQL Server application? Any ideas or tips
>would be appreciated.

>Thanks,
>Kevin Kuebler



Fri, 21 Sep 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Import text file in SQL Server 6.5 via SQL in Visual Basic

2. Import text file in SQL Server 6.5 via SQL in Visual Basic

3. Loading Rich Text Box from SQL Server 6.5 Text Field

4. VB 4.0 & SQL Server 6.5 (Identity Column)

5. Project: Retrieving text fields from SQL Server 6.5

6. Problem with Crystal Reports and Excel OLE-object stored in SQL Server 6.5 and vertical text

7. New Transact-SQL Debugger for Microsoft SQL Server 6.5/7.0

8. SQL Debugger for Microsoft SQL Server 7.0 and 6.5

9. Invalid Column Number in SQL 6.5

10. select Count(*) with date with sql server 6.5 and 7

11. Question regarding Access97 linking to SQL Server 6.5

12. Pessimistic Recordlocks Access --> SQL Server 6.5

 

 
Powered by phpBB® Forum Software