Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: Partial numeric match

    I need help on matching numeric fields based on a partial match.

    I have two tables that match 9-digits in a 12-digit numeric field.

    Example:

    Table 1
    123456789

    Table 2
    999123456789

    I need to match on the last 9 digits. Any suggestions?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try using the MOD function.

    Andy

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Not sure if MOD is a right approach here as it will return the remainder for the first argument.

    convert to CHAR; substr the last 9; then match. Be ready for performance dive.

    Then fix the data.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you use MOD 1000000000, you effectively retain only the last 9 digits. So the conversion to CHAR+substr will be slower for sure because much more processing is involved compared to a simple CPU instruction.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2009
    Posts
    258
    You could also just use INT(RIGHT(Field, 9)) on the match, but I'd agree with stolze.
    Last edited by Ax238; 07-01-09 at 15:29.

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Thanks for clarifying Knut. Never used MOD before. Built a small table and tested and it sure works as a charm.

    Should stop questioning Andy

    Code:
    db2 "select col1, mod(col1, 1000000000) from schema.c"     
    
    COL1                 2                   
    -------------------- --------------------
              1234567890            234567890
             23456789023            456789023
              3534267351            534267351
               400000000            400000000
    
      4 record(s) selected.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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