String query improvement

[dai-chen]

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

Old tracking issue: opendistro-for-elasticsearch/sql#710

1. The IF function with nested locate function doesn't work in Query Workbench opendistro-for-elasticsearch/sql#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: #570

[dai-chen]

TRIM function enhancement: #574

[margarit-h]

The following is the list of string functions specified in opendistro-for-elasticsearch/sql#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:

• like
• not like
• regexp
• substr
• substring
• upper
• lower
• trim
• ltrim
• rtrim

Medium:

• concat
• concat_ws
• length
• strcmp

Low:

• ascii
• bin
• bit_length
• char
• char_length
• character_length
• elt
• export_set
• field
• find_in_set
• format
• from_base64
• hex
• insert
• instr
• lcase
• left
• load_file
• 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
• replace
• reverse
• right
• rlike
• rpad
• soundex
• sounds_like
• space
• substring_index
• to_base64
• ucase
• unhex
• weight_string