Overview | Example | SQL | Optimization | Tips

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 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.

PowerSearch uses a given names synonym list to search for both the formal given name and the most common variations. For example:

The SQL syntax for given name synonyms searches is shown below.

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

PowerSearch uses phonetic algorithms to search for last names that sound similar, but are spelled differently. For example:

Here are some examples:

The SQL syntax for phonetic last name searches is shown below.

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

PowerSearch uses a postal abbreviations synonym list, combined with misspelling and transposition algorithms, to search street addresses.

Here are some examples:

The SQL syntax for postal abbrevation synonym searches and misspelling searches is shown below.

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

PowerSearch uses a cities synonym list, combined with misspelling algorithms, to search city names. For example:

Here are some examples:

The SQL syntax for city synonym searches and misspelling searches is shown below.

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

PowerSearch uses a geographic radius search to locate zip codes that are within a certain distance from the named zip code. This is an example of a PowerSearch search that does not use the $CONTAINS function. Instead, it uses the $DISTANCE function to perform the radius search. This search requires that the database have a table of postal codes that includes their geographic coordinates. For example:

Here are some examples:

The SQL syntax for zip code geographic radius searches is shown below.

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

PowerSearch uses an area code synonym list to locate area codes that are shared within the same metropolitan area. For example:

Here are some examples:

The SQL syntax for telephone area code synonym searches is shown below.

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

PowerSearch uses a transposition search to locate telephone numbers that have been transposed or mistyped. For example:

Here are some examples:

The SQL Syntax for telephone number transposition searches is shown below.

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

PowerSearch uses a standard misspelling search to locate email addresses that have similar spellings. The SQL syntax for email misspelling searches is shown below.

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

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

See also: