This is an old revision of the document!
Overview | Example | SQL | Optimization | Tips
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:
SELECT COUNT(*) FROM LIST WHERE $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES');
SELECT COUNT(*) FROM LIST WHERE $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') AND $contains(LNAME, 'Meyers', 'PHONETIC');
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');
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');
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';
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);
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');
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');
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');
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: