Elasticsearch case-insensitive SQL RLIKE


I am trying for a case-insensitive RLIKE operation in elasticsearch SQL.
WHERE column1 RLIKE '.*Gop.*'

I tried using LCASE, UCASE in WHERE clause on the field, but is giving below error.
"root_cause": [
"type": "sql_illegal_argument_exception",
"reason": "Scalar function [UCASE(column1)] not allowed (yet) as argument for RLIKE"

Thanks in advance.


LIKE and RLIKE expressions are translated to wildcard and regexp queries respectively. If we allowed the usage of scalar functions like LCASE/UCASE, then the query should be instead get translated into a script query which, in general, would have much more poor performance compared to the already expensive regexp/wildcard queries.

A workaround to achieve your goal is to create case insensitive pattern manually:
WHERE column1 RLIKE '.*[g|G][o|O][p|P].*'

You can find more info regarding the regexp pattern here.

If you always want case-insensitive search you can probably add a lowercase normaliser to the mapping for the field in Elasticsearch.

I am planning to use the lowercase normaliser.
Is there a way to define the normaliser in template and automatically apply to all string field of the index?

Yes, you can create an index template where you define dynamic mapping.

Could you share a sample?
I am only able to find templates with normaliser for single column lowercase.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.