KB40693: How wildcards work when placed in string comparison functions in MicroStrategy 9.x.

Wildcards are special characters which when used in a string comparison such as "like" change its behavior. There are two symbols which can be used interchangeably and are recognized by Strategy as wildcards, * and %. There is no functional difference between the two symbols.
 
When used inside a string comparison statement they are a variable that the database recognizes can represent one of three scenarios. The database applies all three scenarios when comparing the data in the filter and there is no way to compare only certain or single of the above scenarios while using wildcards.
 

1. In scenario one the wildcard represents a complete lack of character being present in that location in the comparison. This is illustrated in the example below.
   
   North% like North
   Nor%th like North
   
   These evaluate as true as the % can here represent nothing.
    
2. In scenario two the wildcard represents a single character. This can be any character at all. Again this is best illustrated with examples.
   
   Nort% like North
   No%th like North
   
   These evaluate as true as the % can here represent the single character missing in each scenario.
    
3. In scenario three the wildcard represents multiple characters together. This again can be any combination of characters and there is no limit to the number of characters it may represent. This does mean that a wildcard at the beginning or end of a like comparison makes it functionally equivalent to a begins or ends with comparison. Examples are below.
   
   N%h like North
   No% like North
   
   These evaluate as true as the % can represent the multiple missing characters inside of the left string to make it equal to the right one.

 
Again all three scenarios will always be applied together. This means that any set of strings that can be created through the input string with wildcard will be maintained through the filter. This is illustrated with the below example.
 
Qualification:
Column like n%h
 
Values in database: 
nh, north, nah
 
All strings above evaluate as true in the filter so will be included in the end result calculation.
 
Another point to consider is string comparisons may include multiple wildcards. Each wildcard separately evaluates as any of its three supported scenarios. This means one wildcard can represent nothing while another represents multiple characters or any other valid combination. Further a like comparison where the string includes a wildcard at the beginning and end is functionally equivalent to a contains comparison.
 
When placing these wildcards into SQL Strategy translates them into the appropriate wildcard for the database in question based on the database type selected in the appropriate database instance. An example of this is Nort* used in a qualification against an attribute based on an Access database will be placed into SQL as Nort%. On that note some databases use alternate symbols to the ones above for wildcards. Strategy will not parse those out of a string and will just pass the string as it is received. An example of this is _ in some databases such as Access.