Administration: Omnidex Indexing

PowerSearch

Overview | Example | SQL | Optimization | Tips

The SQL Behind PowerSearch

In most scenarios, the key to implementing PowerSearch in an application is the $CONTAINS function in Omnidex SQL. This function provides access to the many different search capabilities that are shown in the example on the previous page.

The $CONTAINS function is a powerful function with a lot of options. Consult the Omnidex SQL documentation on the $CONTAINS function for instruction on this function.

In the previous example, a succession of SQL statements provided the PowerSearch capabilities:

First Name

select        count(*)
  from        LIST
  where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES');

Last Name

select        count(*)
  from        LIST
  where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
              $contains(LNAME, 'Meyers', 'PHONETIC');

Address

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');

City

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');

State

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';

Zip Code

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);

Phone Area Code

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');

Phone Prefix

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');

Phone Suffix

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');

Email

  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