We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Keyword querying a SQL database

umkibbi0
umkibbi0 asked
on
Medium Priority
256 Views
Last Modified: 2013-12-25
I'm looking for a way to perform full-text keyword searching on particular columns in a SQL database (Sybase, SQL Server), with wildcards, boolean ops and natural language if possible, via ASP scripts on IIS.  

Microsoft Index Server only searches files in directories, not queries on a database.  Their suggestion is to output each database row to an individual file, then index it. Kludge!!

Cold Fusion lets you query a database via the Verity search engine that comes packaged with it.  Very nice, but we gave up CF because it's slow, buggy and lacks a decent programming language.
Comment
Watch Question

Commented:
You want to use the LIKE operator in SQL. That's for wildcard searches. ASP (at least with Access) uses % instead of * and _ instead of ?, which is a little strange. Example:

SELECT * FROM myTable WHERE (message LIKE '%this%') OR (message LIKE '%that%');

LIKE is for exact matches with wildcards. A fuzzy alternative is
SOUNDEX, which acts as a sounds like. It performs a phonetic match:
SELECT * FROM myTable WHERE SOUNDEX(name)=SOUNDEX('umkibbi');

This is nothing compared to the Verity engine in ColdFusion of course, but databases aren't made for full-text retrieval...

Author

Commented:
LIKE and SOUNDEX statements will kill any RDBMS unless you don't care about how long the query takes.  I need OLTP-type response times: under five seconds for each query.  The user interface will be similar to search engines like Altavista, but performing full-text searches on a database.  I anticipate high load, so performance is of the utmost importance.

Commented:
pass the SQL command line to it...

SELECT * FROM "tablename" WHERE value = "searchstring"

That will do it...

Here is a simple script:
<%
Param = Request.QueryString("Param")
Data = Request.QueryString("Data")
CurrentYear = 0
CurrentDate = Date()
%>
<%
If IsObject(Session("YourDSN_conn")) Then
    Set conn = Session("YourDSN_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "YourDSN","",""
    Set Session("YourDSN_conn") = conn
End If
%>
<%
    sql = "SELECT table.field, etc.. list all fields..., table.field "
    sql = sql & "FROM [Missionaries] "
    If cstr(Param) <> "" And cstr(Data) <> "" Then
    sql = sql & " WHERE table.field = "searchstring"
    End If
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>

<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>

your HTML here... substituting the database values etc...

<%
rs.MoveNext
loop%>


- Matt


Author

Commented:
That's no better than the first answer.  "SELECT * WHERE FOO = "keyword" only works on exact matches - at least using LIKE or SOUNDEX can match a subset of the column text, but these are too slow.  RDBMS's are not designed to perform complicated keyword matches - they simply don't have the features and besides that, they don't have the performance.

What I'm looking for is a way to create a keyword index (external to the database) based on an extraction of my database tables, and be able to use a sophisticated search language to query it.  The results from this query should be primary keys that I could use to extract full records from the actual database.

By sophisticated search language I mean Boolean operators (AND,OR,NOT), word stemming (child matches "child, children"), wildcards, stop words (ignore common words "and,the,at") etc.

Commented:
In other words: you want to build your own Verity Topic or Personal Librarian in a SQL database and you want it to be quick. That's not very realistic. Then it's even better to do it the Microsoft way, because you'll end up doing the same thing, only more complicated. You'd be better off looking for a COM object that can do it all, because full-text programs are optimized for inverted files and so.

Commented:
AND THAT'S AN EASY QUESTION ?
This question is worth millions of points :)))))
What you essentially asked is like "can I have a space shuttle to go to the moon, and for fifty bucks, and now please ?"
Then someone offered you a bike, then a deltaplane. Of course, it's not good enough ;))
Writing a good (scalable) search engine like the one described here is no easier than building a space shuttle.
I don't think you'll get an answer to this one :)))

Author

Commented:
Acutually, we have found the answer on our own. It's called Microsoft SiteServer, and we have successfully indexed all text fields in our database (Sybase on Unix), and have created the scripts that use ASP and ADO to take the returned index values, then extract the rows from the database, then spit 'em out to the client.

Millions of points? Looks like we're giving out none. And I don't know about you, but "good enough" is never "good enough" as far as we're concerned. Looks like space shuttles aren't as expensive as you thought.
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.