0

Do you know some explanation why SOUNDEX does not work with NUMBERS as string?

These queries works fine:

select 1 from dual
where soundex('for you') = soundex('for u')
;

select 1 from dual
where soundex('for you') = soundex('for you')
;

But this one doesn´t:

select 1 from dual
where soundex('6000') = soundex('6000')
;
select 1 from dual
where soundex('5') = soundex('5')
;

I was reading documentation http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions167.htm#SQLRF06109 but does not mention some useful about it.

Osy
  • 1,613
  • 5
  • 21
  • 35

3 Answers3

3

The soundex algorithm was specifically developed to match names based on how they sound rather than the exact spelling used. It works basically by mapping strings of letters to short strings containing primarily their consonant sounds. Numeric digits will be completely ignored (e.g. stripped from the string) by the soundex algorithm.

You will need a different strategy for doing approximate matching on strings of digits.

Kaelin Colclasure
  • 3,925
  • 1
  • 26
  • 36
1

Adding to the response, it will take a letter and assign a value according to how they sound. It will not take numbers, they are not considered in the process, that's why it doesn't work.

You can still use other algorithms like Levenshtein distance to compare numbers treated as a string with good results.

https://en.wikipedia.org/wiki/Levenshtein_distance

In Oracle SQL you can use "utl_match".

RaRdEvA
  • 694
  • 7
  • 12
-1

Soundex function returns strings that sounds alike in English and are phonetically similar to each other

For example, "peek" and "pick" are phonetically similar words, so soundex will bring both the results if you query either of the two.

The algorithm of soundex removes the vowels from the words in first step which in case of number will be nothing. But this step can be bypassed.

Now the second step involves assignment of numbers to letters in below manner:

v = 1 | c, g, j, k, q, s, x, z = 2 | d, t = 3 | l = 4 | m, n = 5 | r = 6 |

According to the algorithm no numeric value can be assigned to numbers passed as string in soundex function.

Thus soundex fails for numbers as string but works for character values passed on it.

Courtsey: Oraclemine.com

http://oraclemine.com/soundex-function-oracle/

Ankit
  • 2,753
  • 1
  • 19
  • 26