MongoDB regex string startswith and endswith

Question

So i have something that looks like this

db.usuarios.insert
(
    [
        {
            "nome" : "neymala",
            "idade" : 40,
            "status" : "solteira"
        },
        {
            "nome" : "gabriel",
            "idade" : 31,
            "status" : "casado"
        },
        {
            "nome" : "jose",
            "idade" : 25,
            "status" : "solteiro"
        },
        {
            "nome" : "manoel",
            "idade" : 25,
            "status" : "solteiro",
            "interesses" : [
                "esporte",
                "musica"
            ]
        }
    ]
)

I would like to find names that starts with ma and ends with l, for example "manoel" or "manuel"

I have figured out how to do one or the other with the fallowing querys:

db.usuarios.find({nome:{$regex: /^ma/ }})

db.usuarios.find({nome:{$regex: /l$/ }})

Now i would like to combine them into a single query.

Also refer to [want mongo query to do string regex start && contains && end](https://stackoverflow.com/q/44738577/2313887) since this is one of the worst things you can possibly do. All query forms will not be able to use an index — Neil Lunn, Nov 25 '18 at 19:36

score 27 · Answer 1 · answered Nov 25 '18 at 19:01

27

You can combine the two requirements into a single regex:

db.usuarios.find({nome: /^ma.*l$/})

In a regex, .* means to match 0 or more of any character. So this regex matches names that start with ma and end with l, ignoring whatever is between.

answered Nov 25 '18 at 19:01

JohnnyHK

305,182
66
621
471

score 12 · Answer 2 · answered Nov 25 '18 at 17:05

12

combine both querys with a AND opetator

db.usuarios.find({
$and:[
    {nome:{$regex: /^ma/ }},
    {nome:{$regex: /l$/ }}
]

})

answered Nov 25 '18 at 17:05

user56130

481
1
4
6

4

The answer is right, but `$regex` is not necessary here. `{nome: /^ma/}` is fine. – yaoxing Nov 25 '18 at 18:04

score 7 · Answer 3 · edited Apr 23 '21 at 16:08

7

In javascript /.../ is a regex, so no $regex needed.

db.usuarios.find({
    $and:[
        {nome: /^ma/ },
        {nome: /l$/ }
    ]
})

Also note starts with can hit an index. ends with can't. This way you'd better have a selective starts with, otherwise there will be lots of object scans which may occupy extra CPU and possibly slow down your query.

edited Apr 23 '21 at 16:08

turivishal

34,368
7
36
59

answered Nov 25 '18 at 18:12

yaoxing

4,003
2
21
30

1

A workaround to improve the performance of endswith would be to insert your documents with a duplicate of that field, but with the contents reversed. – robert Apr 29 '21 at 19:01

Atul Kumar · Answer 4 · 2018-11-25T18:19:14.500

0

May be this code help you.

you can search small letter string and capital letter string.......

edited Nov 25 '18 at 18:19

answered Nov 25 '18 at 18:14

Atul Kumar

324
4
8

This works, but just know that there are performance considerations. Regex are not collation aware, so indexes cannot be used when doing case-insensitive regular expression matching. This means that mongo must perform a collection scan. – Steve Storck Apr 07 '22 at 12:04

MongoDB regex string startswith and endswith

4 Answers4