String query improvement

[dai-chen]

Is your feature request related to a problem? Please describe.

Old tracking issue: https://github.com/opendistro-for-elasticsearch/sql/issues/710

1. https://github.com/opendistro-for-elasticsearch/sql/issues/861: Locate with IF function

Describe the solution you'd like N/A

Describe alternatives you've considered N/A

Additional context Add any other context or screenshots about the feature request here.

[dai-chen]

SPACE function that returns a string with the specified number of spaces is missing: https://github.com/opensearch-project/sql/issues/570

[dai-chen]

TRIM function enhancement: https://github.com/opensearch-project/sql/issues/574

[margarit-h]

The following is the list of string functions specified in https://github.com/opendistro-for-elasticsearch/sql/issues/710 The checked ones are implemented in v2 engine. The unchecked ones aren't implemented. position() seems to be the only string function that is implemented in legacy engine, but not in v2.

Highest Prioritized:

• [x] like
• [x] not like
• [ ] regexp
• [x] substr
• [x] substring
• [x] upper
• [x] lower
• [x] trim
• [x] ltrim
• [x] rtrim

Medium:

• [x] concat
• [x] concat_ws
• [x] length
• [x] strcmp

Low:

• [x] ascii
• [ ] bin
• [ ] bit_length
• [ ] char
• [ ] char_length
• [ ] character_length
• [ ] elt
• [ ] export_set
• [ ] field
• [ ] find_in_set
• [ ] format
• [ ] from_base64
• [ ] hex
• [ ] insert
• [ ] instr
• [ ] lcase
• [x] left
• [ ] load_file
• [x] locate
• [ ] lpad
• [ ] make_set
• [ ] match
• [ ] mid
• [ ] not_regexp
• [ ] oct
• [ ] octet_length
• [ ] ord
• [ ] position (is implemented in legacy engine, but not in v2)
• [ ] quote
• [ ] regexp_instr
• [ ] regexp_like
• [ ] regexp_replace
• [ ] regexp_substr
• [ ] repeat
• [x] replace
• [ ] reverse
• [x] right
• [ ] rlike
• [ ] rpad
• [ ] soundex
• [ ] sounds_like
• [ ] space
• [ ] substring_index
• [ ] to_base64
• [ ] ucase
• [ ] unhex
• [ ] weight_string