Elasticsearch case-insensitive SQL RLIKE

Elastic Stack Elasticsearch
1

Hi,

I am trying for a case-insensitive RLIKE operation in elasticsearch SQL.
E.g.
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.

Regards,
Gopal

2

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.

3

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

4

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?

5

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

6

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

7

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