Smith, Smyth, and Schmidt are spelled differently but can sound alike, and a customer search should find all of them. PHONIC_ENCODE, a data quality operator of Oracle AI Database 26ai, returns a phonetic code for a word using the Double Metaphone algorithm. Words that sound alike get the same code, so comparing codes finds names by sound rather than by spelling.
Code for This Guide
The main example is in the examples/character-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
phonic_encode({double_metaphone | double_metaphone_alt}, string [, max_code_len])| Argument | Meaning |
|---|---|
| DOUBLE_METAPHONE | Returns the primary code: the most likely pronunciation |
| DOUBLE_METAPHONE_ALT | Returns the alternate code: a second pronunciation, often of another language origin |
| string | The word to encode |
| max_code_len | The maximum length of the code |
Primary and Alternate Codes
This query encodes five names with both codes.
Example:
select name,
phonic_encode(double_metaphone, name) as primary_code,
phonic_encode(double_metaphone_alt, name) as alternate_code
from (values ('Schmidt'), ('Smith'), ('Smyth'), ('Philips'), ('Filips')) t (name);Output:
NAME PRIMARY_CODE ALTERNATE_CODE __________ _______________ _________________ Schmidt XMT SMT Smith SM0 XMT Smyth SM0 XMT Philips FLPS FLPS Filips FLPS FLPS
Smith and Smyth share the primary code SM0 (the 0 stands for the th sound). Schmidt's primary code XMT follows the German pronunciation, and its alternate code SMT is the anglicized one, while Smith's alternate is XMT. Philips and Filips get the same codes, because ph and f sound the same. Unlike the older SOUNDEX, Double Metaphone handles names of many origins.
Search by Sound
To find names that sound like a search term, compare the stored name's code with both codes of the term.
Example:
-- customers whose last name sounds like Smyth, by either Double Metaphone code
select c.first_name, c.last_name,
phonic_encode(double_metaphone, c.last_name) as code
from customers c
where phonic_encode(double_metaphone, c.last_name)
in (phonic_encode(double_metaphone, 'Smyth'), phonic_encode(double_metaphone_alt, 'Smyth'))
order by c.last_name, c.first_name;Output:
FIRST_NAME LAST_NAME CODE _____________ ____________ _______ Grace Schmidt XMT Layla Schmidt XMT Sofia Schmidt XMT Olivia Smith SM0
Smith matches Smyth's primary code, and the three Schmidts match its alternate code XMT. For a broader search, also compare the stored name's alternate code.
Things to Know
- Codes describe pronunciation, so very different names can share a code; combine PHONIC_ENCODE with FUZZY_MATCH to rank the candidates.
- For fast searches on a large table, store the codes in columns, or index a virtual column, instead of encoding every row in each query.
- The algorithm is designed for single words such as surnames; encode the parts of a full name separately.
Related Guides
Conclusion
PHONIC_ENCODE returns a Double Metaphone code for a word: the primary pronunciation with DOUBLE_METAPHONE and an alternate one with DOUBLE_METAPHONE_ALT. Compare codes to find names that sound alike despite their spelling, and pair it with FUZZY_MATCH to rank the results.
