Wildcard + phrase combination search case insensitive

[viphuangwei]

Hi There,

I am using _xpack/sql/ to do the searching. When there's only phrase searching it's case insensitive.
But if i used wildcard + phrase combination search it's case sensitive Why? I want it both cases should be case insensitive.

e.g. If i use below sql to do searching, if only return 'super hero', not return 'SUPER HERO'

post _xpack/sql/
{
  "query": "select column_A from index_A where query( '*super\\\\  hero*', 'type=phrase;default_operator=AND')"
}

if i remove the wildcard as below. it would return both.

post _xpack/sql/
{
  "query": "select column_A from index_A where query( 'super\\\\  hero', 'type=phrase;default_operator=AND')"
}

---

Is there any solution to do wildcard + phrase combination search case insensitive, without using the analyzer?

[Andrei_Stefan]

Hi @viphuangwei,
The query special function in SQL is a wrapper for a query_string query from Elasticsearch. If you try the translate API from SQL you will see the actual query that ES is using to return the results.

These being said, query_string and phrase matching with wildcard will not work, thus the results you see. See here a suggestion from of the Lucene/ES developers about the type of query to use for such functionality. Unfortunately, ES SQL doesn't use that.