QUERYing with Modulo..

[jeroenvanveghel]

Hi guys!

First of all, thanks for looking into my problem

 

Anyway, this is what i have until now:

 

In my database i have an integer field with max 4 digits. (1001 can be right, 101 can be right etc...)

In this field there are actually 2 int's stored.

In my java code i divide this the following way:

 

totalNummer = 1002;

nummer1 = totalNummer % 100  (=2)

nummer2 = (totalNummer/100) % 100 (=10)

 

(normally i would have made 2 columns but my supervisor didn't agree...)

 

Now i'm trying to create a query which will select all the records for a given number.

So if i give in the number one, the query should be returning all records with the totalNummer of 1, 1001, 110, 201, 108 etc...

 

(oh and btw, i only need to get the records which contain a 1 (but not a 10))

my current query:

 

SELECT * FROM relatiecode
WHERE (relatiecode.TOTALNUMMER=1

OR MOD(relatiecode.TOTALNUMMER, 100) =1
OR MOD((relatiecode.TOTALNUMMER/100), 100) =1

)

 

this gives back all the totalnummers containing 1, 1001, 801 but it leaves out 110, 103

So there is probably a problem in the last part of code, but what !?

[jeroenvanveghel]

Ok, after some tries i ended up with the following.

It solves my problem but it aint that  'neat'

 

OR relatiecode.RELATIECODENUMMER BETWEEN 100 AND 200

OR relatiecode.RELATIECODENUMMER = 1001

OR Concat(relatiecode.RELATIECODENUMMER) REGEXP '1$' )

 

(this topic can be closed)

[kickstart]

Hi

 

MySQL isn't returning an integer from the calculations, hence 1.03 (for example) isn't = 1

 

Try this:-

 

SELECT * 
FROM relatiecode
WHERE relatiecode.TOTALNUMMER =1
OR CONVERT( MOD( relatiecode.TOTALNUMMER, 100 ) , SIGNED ) =1
OR CONVERT( MOD( (relatiecode.TOTALNUMMER /100), 100 ) , SIGNED ) =1

 

All the best

 

Keith

[jeroenvanveghel]

Thanks!!

That actually made it work!