Administration: Omnidex Indexing

PowerSearch

Overview | Example | SQL | Optimization | Tips

The SQL Behind PowerSearch

In most scenarios, the key to implementing PowerSearch is the $CONTAINS function in Omnidex SQL. This function provides access to most of the search capabilities shown on the previous page.

The $CONTAINS Function

The $CONTAINS function is used in the WHERE clause of a SELECT statement to provide criteria accompanied by special options. In traditional SQL statements, criteria is specified using a syntax of:

  SELECT        COUNT(*)
    FROM        LIST
    WHERE       FNAME = 'Bill';

The $CONTAINS function allows this same statement to be reworded as follows:

  SELECT        COUNT(*)
    FROM        LIST
    WHERE       $contains(FNAME, 'Bill');

The real advantage of the $CONTAINS function lies in the options that can be added. The $CONTAINS function is discussed in depth in the documentation on the function. Meanwhile, the following examples show how the options were used to provide each of the capabilities shown on the previous page.

First Name

  SELECT        ...
    FROM        ...
    WHERE       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') ...

Last Name

  SELECT        ... 
    FROM        ... 
    WHERE       $contains(LNAME, 'Meyers', 'PHONETIC') ...

Address

  SELECT        ...
    FROM        ...
    WHERE       $contains(ADDRESS1, '825 Fifth Avenue', 'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') ...

City

  SELECT        ... 
    FROM        ... 
    WHERE       $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') ...

Zip Code

  SELECT        ...
    FROM        ...
    WHERE       ZIP IN
                (SELECT        ZIP
                   FROM        ZIPCODES
                   WHERE       $distance((SELECT LATITUDE,
                                              LONGITUDE
                                  FROM        ZIPCODES
                                  WHERE       ZIP = '10022'),
                               LATITUDE,
                               LONGITUDE) <= 5) ...

Phone Area Code

  SELECT        ...
    FROM        ...
    WHERE       $contains(PHONE1_AREA, '917', 'SYNONYMS=AREACODES') ...

Phone Prefix

  SELECT        ...
    FROM        ...
    WHERE       $contains(PHONE1_PREFIX, '755', 'MISSPELLINGS MIN_SCORE=70') ...

Phone Suffix

  SELECT        ... 
    FROM        ... 
    WHERE       $contains(PHONE1_SUFFIX, '4686', 'MISSPELLINGS MIN_SCORE=70') ...

Email

  SELECT        ...
    FROM        ...
    WHERE       $contains(EMAIL, 'wmeyers@med.cornell.edu', 'MISSPELLINGS MIN_SCORE=80') ...

Final SQL Statement

When combined, the final SQL statement has many $CONTAINS functions which combine to provide the full power of PowerSearch:

  SELECT        COUNT(*)
    FROM        LIST
    WHERE       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') AND
                $contains(LNAME, 'Meyers', 'PHONETIC') AND
                $contains(ADDRESS1, '825 Fifth Avenue',
                'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') AND
                $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') AND
                LIST.STATE = 'NY' AND
                ZIP IN
                (SELECT        ZIP
                   FROM        ZIPCODES
                   WHERE       $distance((SELECT LATITUDE,
                                              LONGITUDE
                                  FROM        ZIPCODES
                                  WHERE       ZIP = '10022'),
                               LATITUDE,
                               LONGITUDE) <= 5) AND
                $contains(PHONE1_AREA, '917', 'SYNONYMS=AREACODES') AND
                $contains(PHONE1_PREFIX, '755', 'MISSPELLINGS MIN_SCORE=70') AND
                $contains(PHONE1_SUFFIX, '4686', 'MISSPELLINGS MIN_SCORE=70') AND
                $contains(EMAIL, 'wmeyers@med.cornell.edu', 'MISSPELLINGS                 MIN_SCORE=80');

Prev | Next