Wildcards (? and *) support

[wojciechczerniak]

Description

ODFF specifies wildcards in their global (evaluator) settings: #58

HOST-USE-WILDCARDS: If true, wildcards question mark '?' and asterisk '*' are used for character-string comparisons and when searching. Wildcards may be escaped with a tilde '~' character.

Related functions

All database functions: DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP

The results of database functions may change when the values of the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties change. 3.4

Then this depends on the function description, but we can extract the list: COUNTIF, COUNTIFS, HLOOKUP, LOOKUP, MATCH, VLOOKUP, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, FIND, SEARCH.

All of them has short note:

The values returned may vary depending upon the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. 3.4

Links

http://help.grapecity.com/spread/SpreadSheets12/webframe.html#wildcard.html

[wojciechczerniak]

useWildcards in global config. Performance may be lower, so this should be disabled by default.

[izulin]

@wojciechczerniak

what exactly can be in the pattern (with and without the flag for wildcards?)

[wojciechczerniak]

@izulin What do you mean? There are three special characters introduced asterisk: "*", quotation mark: "?" and the tilde: "~". Without the flag, they are considered as any other character.

When it comes to the use cases, there are multiple sources all over the internet. Some are simple, some pretty advanced:

https://www.excelawesome.com/12-excel-wildcard-examples-you-need-to-know/
https://exceljet.net/glossary/wildcard
https://softwareaccountant.com/vlookup-wildcard/
https://www.spreadsheetweb.com/wildcard-in-excel-formula/
https://www.spreadsheetweb.com/excel-wildcard-criteria/
https://help.grapecity.com/spread/SpreadSheets12/webframe.html#wildcard.html

[izulin]

@izulin What do you mean? There are three special characters introduced asterisk: "*", quotation mark: "?" and the tilde: "~". Without the flag, they are considered as any other character.

When it comes to the use cases, there are multiple sources all over the internet. Some are simple, some pretty advanced:

https://www.excelawesome.com/12-excel-wildcard-examples-you-need-to-know/
https://exceljet.net/glossary/wildcard
https://softwareaccountant.com/vlookup-wildcard/
https://www.spreadsheetweb.com/wildcard-in-excel-formula/
https://www.spreadsheetweb.com/excel-wildcard-criteria/
https://help.grapecity.com/spread/SpreadSheets12/webframe.html#wildcard.html

Do we need the flag? There is no such flag in EXCEL/GS, right?

[wojciechczerniak]

The flag is a part of ODFF spec.

LC has it:

[izulin]

ok

[wojciechczerniak]

@izulin There are two additional flags that may be related:

HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL If true, the specified search criteria shall apply to the entire cell contents if it is a text match using = or <>; if not, only a subpart of the cell content needs to match the text.

HOST-USE-REGULAR-EXPRESSIONS: If true, regular expressions are used for character string comparisons and when searching.

What if underneath it's always a Regexp?

• If HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is true then that is a strict match
• If HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is false we're searching for any match
• If HOST-USE-REGULAR-EXPRESSIONS is enabled we use any Regexp available
• If HOST-USE-WILDCARDS is enabled we limit the Regexp to * and ?

[izulin]

@wojciechczerniak one last question: what happens, if we improperly use '~' for escaping?
e.g. pattern being only '~' , or escape something that does not need escaping?

[izulin]

@wojciechczerniak
what happens to caseSensitivity/accentSensitivity?

[wojciechczerniak]

@wojciechczerniak one last question: what happens, if we improperly use '' for escaping?
e.g. pattern being only '' , or escape something that does not need escaping?

Escaped character is still that character.

@wojciechczerniak
what happens to caseSensitivity/accentSensitivity?

Comparison operators are subjected to caseSensitive config option. When it comes to functions, each has its own constraints. f.e. MATCH: "If a match is found, MATCH returns the relative position (starting from 1). For Text the comparison is case-insensitive."

AccentSensitivity is not mentioned within ODFF. Looks like the culture settings are global and IMO is a good rule of thumb.

[wojciechczerniak]

Done in #303