Querie mongodb with regex

Question

I’m trying to find the best way to make that wish on mongodb. I have this number 42999234180 and I have a table of prefixes ranging from 3 to 7 characters, I would like when searching for 4299234180 it returns the value that is more similar to the integer number, for example in the bank I have saved

429
4299
42999 <--- nesse caso ele retornaria esse resultado.
429998

Answer 1

I think you’re looking for something like string search by approximate result (try looking for "approximative string matching"). Thinking for a moment, if you search for the whole number there is no possibility of finding "smaller" results, unless you are using some approximate result.

There are some techniques for this, I used that algorithm in a project. It compares pairs of letters within its String. Take a look at.

Answer 2

What you want has nothing to do with REGEX, as it is necessary to perform arithmetic operations.

Resolution

CREATE TABLE prefixos(
  prefixo INTEGER
);

INSERT INTO prefixos (prefixo) VALUES
('429'),
('4299'),
('42999'),
('429998');

SELECT
    P.prefixo,
    min(P.prefixo - Q.pos)
FROM    
    prefixos P
    LEFT JOIN (
        SELECT  prefixo,
            substring('4299234180' from 0 for char_length(prefixo::varchar)+1)::integer as pos
        FROM    prefixos
    ) as Q ON Q.prefixo = P.prefixo
WHERE
    (P.prefixo - Q.pos) > 0
GROUP BY
    P.prefixo
ORDER BY
    2
LIMIT   1

Explanation

To solve your problem I used the following logic :

• Capture the part of the number corresponding to the prefix size. Prefix 429 has Len = 3, so I’ll capture the first 3 characters of your number.

The part about doing that is :

LEFT JOIN (
    SELECT  prefixo,
        substring('4299234180' from 0 for char_length(prefixo::varchar)+1)::integer as pos
    FROM    prefixos
) as Q ON Q.prefixo = P.prefixo

• After having the corresponding number to the prefix I perform an arithmetic operation to remove the ones equal to zero because I want to say that a higher prefix should suit.
  (P.prefixo - Q.pos) > 0
• Finally, I check the least variation possible so having the prefix that best adapts. min(P.prefixo - Q.pos)

Note

• The base I used was Postgresql, maybe you need to adapt something, because not all DBMS have the same functions.