{"id":19155,"date":"2021-07-31T10:10:01","date_gmt":"2021-07-31T00:10:01","guid":{"rendered":"https:\/\/database.guide\/?p=19155"},"modified":"2021-07-31T10:10:01","modified_gmt":"2021-07-31T00:10:01","slug":"soundex-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/soundex-function-in-oracle\/","title":{"rendered":"SOUNDEX() Function in Oracle"},"content":{"rendered":"\n

In Oracle, the SOUNDEX()<\/code> function returns a character string containing the phonetic representation of its argument. This is known as the Soundex string.<\/p>\n\n\n\n

Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. If two words sound the same, they should have the same Soundex string. If two words sound similar, but not exactly the same, their Soundex string might look similar but not exactly the same.<\/p>\n\n\n\n

The function accepts one argument: the string for which to return the Soundex string from.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

SOUNDEX(char)<\/code><\/pre>\n\n\n\n
Where char<\/code> is the string for which to return the Soundex string from. It can be of any of the data types CHAR<\/code>, VARCHAR2<\/code>, NCHAR<\/code>, or NVARCHAR2<\/code>. The return value is the same data type as the argument.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a basic example:<\/p>\n\n\n\n
SELECT SOUNDEX('Bat')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
B300<\/pre>\n\n\n\n
Here’s another example that compares the Soundex string returned from similar, but different, sounding words:<\/p>\n\n\n\n
SELECT \n    SOUNDEX('Bat'),\n    SOUNDEX('Cat'),\n    SOUNDEX('Cap')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   SOUNDEX('BAT')    SOUNDEX('CAT')    SOUNDEX('CAP') \n_________________ _________________ _________________ \nB300              C300              C100             <\/pre>\n\n\n\n
And here’s one that compares words that don’t sound alike:<\/p>\n\n\n\n
SELECT \n    SOUNDEX('Ponzi'),\n    SOUNDEX('Bracket'),\n    SOUNDEX('Heavy')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   SOUNDEX('PONZI')    SOUNDEX('BRACKET')    SOUNDEX('HEAVY') \n___________________ _____________________ ___________________ \nP520                B623                  H100                <\/pre>\n\n\n\n
Exact Matches<\/h2>\n\n\n\n
Here’s an example of pairs of words that have a matching Soundex string, even though they’re different words, with different meanings:<\/p>\n\n\n\n
SELECT \n    SOUNDEX('Dam') AS Dam, \n    SOUNDEX('Damn') AS Damn, \n    SOUNDEX('Too') AS Too, \n    SOUNDEX('Two') AS Two\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
    DAM    DAMN     TOO     TWO \n_______ _______ _______ _______ \nD500    D500    T000    T000   <\/pre>\n\n\n\n
Database Example<\/h2>\n\n\n\n
Here’s an example of getting the Soundex string from a database query:<\/p>\n\n\n\n
SELECT \n    country_name, \n    SOUNDEX(country_name)\nFROM countries\nFETCH FIRST 10 ROWS ONLY;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   COUNTRY_NAME    SOUNDEX(COUNTRY_NAME) \n_______________ ________________________ \nArgentina       A625                     \nAustralia       A236                     \nBelgium         B425                     \nBrazil          B624                     \nCanada          C530                     \nSwitzerland     S326                     \nChina           C500                     \nGermany         G655                     \nDenmark         D562                     \nEgypt           E213                     <\/pre>\n\n\n\n
We can also use SOUNDEX()<\/code> in a WHERE<\/code> clause to return only those rows that sound like a given word:<\/p>\n\n\n\n
SELECT\n    employee_id,\n    first_name,\n    last_name\nFROM employees\nWHERE SOUNDEX(first_name) = SOUNDEX('Stephen');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   EMPLOYEE_ID    FIRST_NAME    LAST_NAME \n______________ _____________ ____________ \n           100 Steven        King         \n           128 Steven        Markle       \n           138 Stephen       Stiles      <\/pre>\n\n\n\n
Empty String<\/h2>\n\n\n\n
Here’s what happens when an empty string is passed for each given argument:<\/p>\n\n\n\n
SET NULL 'null';\nSELECT SOUNDEX('')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
null<\/pre>\n\n\n\n
By default, SQLcl and SQL*Plus return a blank space whenever null<\/code> occurs as a result of a SQL SELECT<\/a><\/code> statement. <\/p>\n\n\n\n
However, you can use SET NULL<\/a><\/code> to specify a different string to be returned. Here I specified that the string null<\/code> should be returned.<\/p>\n\n\n\n
Null Argument<\/h2>\n\n\n\n
Passing null<\/code> returns null<\/code>:<\/p>\n\n\n\n
SELECT SOUNDEX(null)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
null<\/pre>\n\n\n\n
Missing Argument<\/h2>\n\n\n\n
Calling SOUNDEX()<\/code> without passing any arguments results in an error:<\/p>\n\n\n\n
SELECT SOUNDEX()\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Error starting at line : 1 in command -\nSELECT SOUNDEX()\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00909: invalid number of arguments\n00909. 00000 -  \"invalid number of arguments\"\n*Cause:    \n*Action:<\/pre>\n\n\n\n
And passing too many arguments also results in an error:<\/p>\n\n\n\n
SELECT SOUNDEX('Gosh', 'Dang')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Error starting at line : 1 in command -\nSELECT SOUNDEX('Gosh', 'Dang')\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00909: invalid number of arguments\n00909. 00000 -  \"invalid number of arguments\"\n*Cause:    \n*Action:<\/pre>\n","protected":false},"excerpt":{"rendered":"
In Oracle, the SOUNDEX() function returns a character string containing the phonetic representation of its argument. This is known as the Soundex string. Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. If two words sound the same, they should have the same Soundex string. If two words sound similar, … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[93,67,20],"class_list":["post-19155","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19155","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=19155"}],"version-history":[{"count":2,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19155\/revisions"}],"predecessor-version":[{"id":19158,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19155\/revisions\/19158"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=19155"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=19155"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=19155"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}