We have gone through how amazing Soundex function really is. When used with letters it returns strings that are phonetically equivalent to each other and sounds alike in English. Also, we gained knowledge about the wonderful algorithm that works behind its back. Believe me, if you are a technical developer having an understanding of this function sometimes proves to be extremely useful. But due to some reason Soundex function does not retrieve any output if used with numbers.
What is the problem of Soundex with numbers then?
Before we figure out reason for why Soundex does not work with numbers let us go through its algorithm first
- Retain the first letter of the string
- Remove all other occurrences of the following letters: a, e, h, i, o, u, w, y (or change it to zero ‘0’)
- Assign digits to the remaining letters (after the first) as follows:
b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6
- If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.
- Replace the first digit with the letter (as in Step 1)
- If the string is less than 4 letters pad ‘0’ on the right. If it is more than 4 letters return only the first four positions.
If you look closely at Step 2 we can see that a numeric digit is assigned to a letter or set of letters except for those mentioned in Step 1. But for numbers, there is no such assignment of numeric digits.
So if we use numbers as characters in Soundex function there will be nothing assigned to them and query will not retrieve any rows.
For example, below query will give no output:
SELECT 1 FROM dual WHERE Soundex('100') = Soundex('100');
Did you like the above post? Leave the comments below. Your suggestions and feedback are always welcome.
Hi I am Paras.
Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at email@example.com. Again I appreciate your visit. Hope to see you again and again!