Re: Spring Mongo case insensitive search using regex problem

[Ian Daniel]

Hi Rakendu,

The regex /^bill$/i will match just against "Bill" in a case-insensitive manner.

Here is an example showing this (in the mongo shell):

    > db.foo.insert({name: "Bill"});
    > db.foo.insert({name: "Bill status"});
    > db.foo.insert({name: "another Bill"});
    > db.foo.find()
    { "_id" : ObjectId("5018e182a499db774b92bf25"), "name" : "Bill" }
    { "_id" : ObjectId("5018e191a499db774b92bf26"), "name" : "Bill status" }
    { "_id" : ObjectId("5018e19ba499db774b92bf27"), "name" : "another Bill" }
    > db.foo.find({name: /bill/i})
    { "_id" : ObjectId("5018e182a499db774b92bf25"), "name" : "Bill" }
    { "_id" : ObjectId("5018e191a499db774b92bf26"), "name" : "Bill status" }
    { "_id" : ObjectId("5018e19ba499db774b92bf27"), "name" : "another Bill" }
    > db.foo.find({name: /^bill$/i})
    { "_id" : ObjectId("5018e182a499db774b92bf25"), "name" : "Bill" }

However, a regex query will not use an index, except if it is left-rooted (ie. of the form /^prefix/) and if the i case-insensitive flag is not used. You may pay a substantial performance penalty over using a query that uses an index. As such, depending on your use case, a better alternative might be to use application logic in some way, for example:
- enforce a case when you insert items into the database (e.g. convert "bill" to "Bill")
- do a search against your known case (e.g. search just against "Bill")

Kind regards,
Ian

On Wednesday, August 1, 2012 4:32:43 AM UTC+10, rakendu r wrote:

Hello,

I am trying a case insensitive search in Mongo. Basically I want case insensitive string match I am using regex. Here is my code

Query query = new Query( Criteria.where(propName).regex(value.toString(), "i"));

But the above dosent match my whole string(a string sometime with spaces). It returns values even if its a substring.

Eg: Suppose my collection has 2 values "Bill" and "Bill status',It returns me "bill" even if my search is "bill status". It returns results even if the there is a sub string of the string I am searching for

I tried Query query = new Query( Criteria.where(propName).is(value.toString()));

But the above is case sensitive. Can someone please help on this.

[Ian Daniel]

Also answered on Stack Overflow: http://stackoverflow.com/questions/11742170