This is an old revision of the document!
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.
SELECT ... FROM ... WHERE $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') ...
SELECT ... FROM ... WHERE $contains(LNAME, 'Meyers', 'PHONETIC') ...
SELECT ... FROM ... WHERE $contains(ADDRESS1, '825 Fifth Avenue', 'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') ...
SELECT ... FROM ... WHERE $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') ...
SELECT ... FROM ... WHERE ZIP IN (SELECT ZIP FROM ZIPCODES WHERE $distance((SELECT LATITUDE, LONGITUDE FROM ZIPCODES WHERE ZIP = '10022'), LATITUDE, LONGITUDE) <= 5) ...
SELECT ... FROM ... WHERE $contains(PHONE1_AREA, '917', 'SYNONYMS=AREACODES') ...
SELECT ... FROM ... WHERE $contains(PHONE1_PREFIX, '755', 'MISSPELLINGS MIN_SCORE=70') ...
SELECT ... FROM ... WHERE $contains(PHONE1_SUFFIX, '4686', 'MISSPELLINGS MIN_SCORE=70') ...
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');
See also: