FatFractal customer forums



Author Topic: Case insensitive "matches" Query  (Read 1279 times)

tomkif

  • Newbie
  • *
  • Posts: 9
    • View Profile
Case insensitive "matches" Query
« on: March 01, 2014, 12:40:25 PM »
Hi, As I didn't find it in the doc I am looking for a non-case sensitive matches operator to do a text search on a field. I tree with (name matches '.*%@.*') where %@ is a user input. I tried with contains_any operator, it is non case sensitive BUT take only full word and no wildcard. I am thinking about a shortcut with something like (UPPER(name) matches '.*UPPER(%@).*') Thanks for your help

gkc

  • Administrator
  • *****
  • Posts: 375
    • View Profile
Re: Case insensitive "matches" Query
« Reply #1 on: March 01, 2014, 12:46:45 PM »
Hi Tom,

In essence, well, the problem with case-insensitive queries is the performance overhead, because by definition all of the data has to be retrieved, and upper-cased, before it can be compared. You get the same problem with regex queries.

FYI: contains_all and contains_any will take more than one word but you’re correct there is no wild-carding. In general for problems like this I’d suggest denormalizing the field - i.e. create another field which contains an uppercase version of the source field - and you will need the “starts_with” query language feature which has been on the wish list for a while and which I've just bumped up the list a bit

In the interim, you can do a case-insensitive query like this:
    http://acme.fatfractal.com/hoodyoodoo/ff/resources/Celebrity/(lastName matches '(%3Fi)a.*')
It should actually look like this
    http://acme.fatfractal.com/hoodyoodoo/ff/resources/Celebrity/(lastName matches ‘(?i)a.*')
but ‘?’ needs to be url-encoded to %3F

But, unless you have already limited the dataset either with another predicate, or via a traversal, beware of the performance impact. In the above example, all Celebrities are retrieved in order to run the regex comparison

Cheers,

- Gary

 

Copyright © FatFractal customer forums