Why SOUNDEX('1') returns NULL value?
Why SOUNDEX('1') returns NULL value?
Last edited by krishnaindia2007; 06-04-2008 at 05:55 AM.
Here is a beautiful example based on the above point.
How to select only Numeric values from the following table containing alphanumeric data?
Code:CREATE TABLE MY_TABLE ( TEST VARCHAR2(3) ); INSERT INTO MY_TABLE VALUES('1'); INSERT INTO MY_TABLE VALUES('2'); INSERT INTO MY_TABLE VALUES('2a'); INSERT INTO MY_TABLE VALUES(‘3a’); COMMIT; My required output is 1 2 Query is SQL> SELECT test FROM my_table WHERE soundex(test) IS NULL; TEST --- 1 2 DROP TABLE MY_TABLE;
Rules For Soundx:
Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y
Assign numbers 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.
Return the first four bytes padded with 0.