Hello everyone,

I am using DB2 SOUNDEX function, it works fine for me except in case i am passing a single character numeric value such as in following example

Select SOUNDEX('1') from SYSIBM.SYSDUMMY1;

Output Values are random such as
'.121' -- Execution 1
'.122' -- Execution 2
'.621' -- Execution 3

I get different values on executing the same statment multilpe times.

Please help me in ensuring that result always comes out same for above query. Also i am very interested in finding out why DB2 is behaving this way?

Appreciate any help

Much Thanks,
AB

ABisht, what Edition, Version of DB2 are you using? I just tried your statement on DB2 z/OS v9 and DB2 Express-C LUW V9.7 and returned 'Z000' every time on both systems. I processed the query in QMF on z/OS and Command Editor on LUW.

Stealth_DBA

We are using DB2 Version 9.1 for z/OS. If i code my query as

SELECT SOUDEX(1) FROM SYSIBM.SYSDUMMY1;

I always get result as Z000, but when i code it as

SELECT SOUDEX('1') FROM SYSIBM.SYSDUMMY1;

results differ.

If you consider the soundex algorithm, you'll probably find that it is defined only for alphabetic characters. Subsequently, for numeric (non-alphabetic) characters its result is undefined, which is exactly what you are seeing.

Thanks N_I,

Any solutions to avoid this undefined result set.

I see two:
- don't use the soundex algorithm
- supply only alphabetic characters to soundex()

Thats an easy one but am not sure the desired one.

I will have to go back to bosses and discuss this further.

Thanks N_I for your replies. They have helped