SQL 7.0 Full Text Search and Highlighting

SQL 7.0 Full Text Search and Highlighting

Post by Sarbjit Samr » Tue, 23 Mar 1999 04:00:00



I am doing full text searches on character data stored in SQL 7.0
server tables (char and text columns). This is done by specifying text
search criteria in SELECT statement through CONTAINS or FREETEXT
predicates. I am receiving the results (set of rows) as expected. No
probelm. What I want to do is to display the documents that I am
searching and highlight the matched words and phrases. However SQL 7.0
does not appears to return text markers. Has anybody had any success
with this feature? Thank you.
 
 
 

SQL 7.0 Full Text Search and Highlighting

Post by Sarbjit Samr » Tue, 23 Mar 1999 04:00:00


I am doing full text searches on character data stored in SQL 7.0 server
tables (char and text columns). This is done by specifying text search
criteria in SELECT statement through CONTAINS or FREETEXT predicates. I
am receiving the results (set of rows) as expected. No probelem.

 What I want to do is to display the documents that I am searching and
highlight the matched words and phrases. However SQL 7.0 does not
appears to return text markers around matched words. Has anybody had any
success with this feature?

Rephrasing what I asked above is how to highlight matched words using
SQL 7.0?


 Thank you.

 
 
 

1. Hit highlighting with full-text searches

I'm creating a 100 million word corpus of historical Spanish texts that
uses full-text queries with SQL Server 7.0, and have a question re. hit
highlighting.

What I need is a way to access the "character offset" info in the full-
text index, which contains the location WITHIN THE RECORD of all
matching hits (e.g. record 314, starting at character 437, record 476,
starting at charatcer 1245, etc).

Using just the INSTR and CHARINDEX functions to find the string within
the record are only adequate for an exact string, e.g.
        "likes cultures"
Here you'd just use INSTR or CHARINDEX to look for this exact string
and then add the highlighting codes.

The problem comes with wildcard and proximity searches.  For example,
imagine that you're searching for
        like culture
        likes cultures
        liked cultures
        liking culture, etc.
The query would be:
        LIK CULTURE*
The CONTAINS query _will_ find the records matching any of these
variants, but the problem is finding and highlighting the hits within
the record itself.  Suppose that the record contains the following
strings:
        likes Pepsi
        liking summer vacation
        liked cultures
        like all of the others
CHARINDEX and INSTR want an exact string to search for, and there isn't
one.  You can do all sorts of algorithms to try and find the string
within the record (e.g LIK CULTURE*) and highlight it, but I haven't
found anything that works well.  The only solution that I can think of
is to use info from the index itself containing the location of the
string in each record.

Any comments?  Thanks in advance,

Mark Davies
Illinois State University

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Re-Use Printer Option

3. Full-text Search, Index Service and hit-highlighting

4. Q: Speed Up Recordet Fetch Ado+SqlSrv

5. Hit highlighting with full-text searches

6. 70 seconde rule

7. How to install Ent. Mgr. only on Win98 Client PC

8. difference between SQL Server 7.0 and SQL Server 2000 full-text search

9. SQL 7.0 and full text searching

10. SQL 7.0 and Full Text Search Engine

11. Full-text search with clustered SQL 7.0 SP1