Soundex

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.