8

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.

Droidme
  • 1,223
  • 6
  • 25
  • 45

2 Answers2

10

Insensitive search using regex search. input_location could be Delhi,delhi,DELHI, it works for all.

Criteria.where("location").regex( Pattern.compile(input_location, Pattern.CASE_INSENSITIVE | Pattern.UNICODE_CASE)));
Ajay Kumar
  • 4,864
  • 1
  • 41
  • 44
  • 2
    What if I just want to search case-insensitive. For example if I find for `male` I should get `male` and `MALE`, but I am also getting `FEMALE` because it contains male. – Half Blood Prince Jul 18 '16 at 09:06
3

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:

  1. Enforce a case when you insert items into the database (e.g. convert "bill" to "Bill").

  2. Do a search against your known case (e.g. search just against "Bill").

Ian Daniel
  • 586
  • 3
  • 3
  • thanks for taking ur time to reply in both places:). I am using spring-mongo to interact with the mongo db. Can I use the same there? if so can you tell me how I can change it in my code? I tried "i(/^value.tostring$)" from the above command that I have used. thanks – Droidme Aug 01 '12 at 08:41
  • @Droidme: as an alternative to spring-mongo, you may want to have a look at [Jongo](http://jongo.org/). Jongo's current mission statement is "Query in Java as in Mongo shell". – Stennie Aug 02 '12 at 03:57
  • 7
    I am not a spring-mongo developer, however I suggest you try: Query query = new Query(Criteria.where(propName).regex("^" + value.toString() + "$", "i")); – Ian Daniel Aug 02 '12 at 07:33