Why SOUNDEX('1') returns NULL value?
Printable View
Why SOUNDEX('1') returns NULL value?
[QUOTE=babi_geek;30905]Why SOUNDEX('1') returns NULL value?[/QUOTE]
If you have an alpha character soundex returns a value otherwise returns a null.
[CODE]
SQL> SELECT SOUNDEX('123456') FROM DUAL;
S
-
[B]<- OUTPUT NULL[/B]
SQL> SELECT SOUNDEX('1234X') FROM DUAL;
SOUN
----
X000
SQL> SELECT SOUNDEX('A23333') FROM DUAL;
SOUN
----
A000
[/CODE]
Here is a beautiful example based on the above point.
[B]How to select only Numeric values from the following table containing alphanumeric data?[/B]
[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;
[/CODE]
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.