Display an excerpt from search results

Posted on 2007-07-23
Last Modified: 2012-05-05
I have a full text index that users can search. Everything works fine but I don't know how to go about retrieving and displaying an excerpt where the keywords were found. I would also like to highlight those keywords within the excerpt.

The result would be similar to what is displayed in this site when a search is performed.

I am using classic ASP and MSSQL 2005

Question by:millmatic
    LVL 32

    Expert Comment

    by:Big Monty
    I assume you know how to get the data from the database and store it into a variable? if so, this should work for you:

    keywords = Request.Form("searchKeywords")
    results = rs("colNameFromDB")

    results = Left(results, 200)       'takes the first 200 characters of the results
    arrKeywords = Split(keywords, ",")    'assumes keywords are seperated by commas

    for each element in arrKeywords
         if InStr(results, element) > 0 then      'if keyword is in the results excerpt.......
            results = Replace(results, keyword, "<font color=red><b>" & keyword & </b></font>"    'change the keyword to red and bold it
        end if

    Response.Write results


    Author Comment

    Thanks, but I forgot to mention that the columns are binary data from file uploads. The files are uploaded into the database as "blob" I used the Full-text Index so that the files can be searched and it does work. I just don't know how to get the excerpt from the file to display in the results.

    I will use the script you gave me but I still need to know how I can retrieve the actual type from the file I'm searching.

    Thanks ffor your help Big Daddy
    LVL 32

    Accepted Solution

    ahhh ok, thats going to be tough. Basically you'll need to use the FileStream object and buffer it to a variable, then apply my script above. i know how to buffer it to the screen (just google "asp, write blob to screen") but not to a variable.

    maybe others can help.


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now