sql 'like' operations on numbers in mongodb

Question

Im trying to do a query in mongodb similar to SQL example:

SELECT * from example where contactphone like '4832%'; 

My first idea was to do something like:

db.example.find({ contactphone: /^4832/ })

but it doesnt return what I am looking for, as contactphone is a number.

Is there any good practices-way to do such operations in mongodb on numbers?

EDIT: phone number may be a bad example. Lets say i need to find all numbers that CONTAIN number digit 9. How can i achive that?

Answer 1

Do not store telephone numbers as integers. Store them as strings. When you have a value which consists only of digits and on which arithmetics are pointless, like a zip-code, a bank account number or a telephone number, then storing it as a number does not make much sense.

• As you already noticed, numbers are almost impossible to search when you want to search for a digit-sequence, because computer systems store integers in binary, not in decimal.
• You can not have leading zeros
• When the number is too long, you will either encounter an integer overflow or you will have it converted to a floating-point number (at the discretion of your MongoDB driver). Both will result in very strange behavior.

All of this applies to almost any database, by the way, not just MongoDB.

But should you be absolutely determined to keep them as numbers, here are two things you can do.

1. When the number of unknown digits is fixed, you could use the $gt and $lt operators to search for a range. contactphone:{$gt:5556000, $lt:5556999} would find all numbers with the pattern 5556xxx.
2. You could use a $where-query which uses a javascript function to internally convert every number to a string and then applies your regular expression to that string. $where: "String(this.contactphone).match(/^4832/) != null". I hope you don't have many documents in your database, because when you do, this query could take a while.