Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Question Unanswered: Ambiguous soundex results

    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

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Nov 2011
    Posts
    4

    Reply for Stealth_DBA

    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Nov 2011
    Posts
    4

    Reply for N_I

    Thanks N_I,

    Any solutions to avoid this undefined result set.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ABisht View Post

    Any solutions to avoid this undefined result set.
    I see two:
    - don't use the soundex algorithm
    - supply only alphabetic characters to soundex()

  7. #7
    Join Date
    Nov 2011
    Posts
    4

    Reply for N_I

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •