CODEWITHSUNDEEP
mysqlsql
Lamar
Lamar

Reputation: 1849

Compare the digits of two integers in each decimal position

I am not sure I am describing the problem using the correct terms, my math English is not that good.

What I need to do is check if they match for each digit of two integers based on the position of the digit: ones, tens, .. etc

For example check the following table of different numbers and the wanted comparison result:

number1 | number2 | desired result
-----------------------------------
100     | 101     | 001
443     | 143     | 300
7001    | 8000    | 1001
6001    | 8000    | 2001
19      | 09      | 10

Basically I need the absolute value of subtraction for each digit alone. So for the first example:

1 0 0
1 0 1 -
--------
0 0 1

And second:

4 4 3
1 4 3 -
-------
3 0 0

And third:

7 0 0 1
8 0 0 0 -
---------
1 0 0 1

This needs to be done in mysql. Any ideas please?

Upvotes: 2

Views: 90

Answers (2)

Jan Zeiseweis
Jan Zeiseweis

Reputation: 3738

This should do the job if your numbers are below 10000. If they exceed, simply modify the query ;)

    SELECT number1,
           number2,
           REVERSE(CONCAT(ABS(SUBSTRING(REVERSE(number1), 1, 1) - SUBSTRING(REVERSE(number2), 1, 1)),
           IF(CHAR_LENGTH(number1) > 1, ABS(SUBSTRING(REVERSE(number1), 2, 1) - SUBSTRING(REVERSE(number2), 2, 1)), ''),
           IF(CHAR_LENGTH(number1) > 2, ABS(SUBSTRING(REVERSE(number1), 3, 1) - SUBSTRING(REVERSE(number2), 3, 1)), ''), 
           IF(CHAR_LENGTH(number1) > 3, ABS(SUBSTRING(REVERSE(number1), 4, 1) - SUBSTRING(REVERSE(number2), 4, 1)), ''))) as `desired result`
    FROM numbers

for 3 digit numbers:

SELECT number1,
       number2,
       CONCAT(
           ABS(SUBSTRING(number1, 1, 1) - SUBSTRING(number2, 1,1)), 
           ABS(SUBSTRING(number1, 2, 1) - SUBSTRING(number2, 2,1)),
           ABS(SUBSTRING(number1, 3, 1) - SUBSTRING(number2, 3,1))
       )
FROM numbers

actually you don't have reverse the string at all. this comes from a more mathematical approach I tried before ;)

Upvotes: 1

Jayvee
Jayvee

Reputation: 10873

if you want to do it with integers only, it can be done this way (for 5 digits as an example):

select abs(number1/10000 - number2/10000) * 10000 +
       abs(number1/1000 % 10 - number2/100 % 10) * 1000 +
       abs(number1/100 % 10 - number2/100 % 10) * 100 +
       abs(number1/10 % 10 - number2/10 % 10) * 10 +
       abs(number1 % 10 - number2 % 10)

Upvotes: 0

Related Questions