Partial numeric match

art500 · #1 (**permalink**) 07-01-09, 12:04

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?

ARWinner · #2 (**permalink**) 07-01-09, 12:46

Try using the MOD function.

Andy

Cougar8000 · #3 (**permalink**) 07-01-09, 13:48

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.

stolze · #4 (**permalink**) 07-01-09, 14:24

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.

Ax238 · #5 (**permalink**) 07-01-09, 14:26

You could also just use INT(RIGHT(Field, 9)) on the match, but I'd agree with stolze.

Cougar8000 · #6 (**permalink**) 07-01-09, 14:40

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech