IsMatch

Performs wild-card search / pattern matching to check if a string matches a given pattern. The input string can be a member name, an alias, an attribute value, or any relevant string. This function searches for strings matching the pattern you specify, and returns the artifacts it finds.

Syntax

IsMatch(string, patternstring, {MATCH_CASE | IGNORE_CASE})

Parameter	Description
string	The string that should be tested against the pattern.
patternstring	The pattern to search for. Must be in POSIX Extended Regular Expression Syntax. See the syntax specification at The Open Group. See the Notes in this topic for additional rules regarding special characters.
MATCH_CASE	Optional. Consider patternstring to be case sensitive. If MATCH_CASE / IGNORE_CASE are omitted, Essbase defaults to the case-sensitive setting of the outline properties. To define database member names as case-sensitive, use Outline Editor in Administration Services (see the Oracle Essbase Administration Services Online Help).
IGNORE_CASE	Optional. Do not consider patternstring to be case sensitive. If MATCH_CASE / IGNORE_CASE are omitted, Essbase defaults to the case-sensitive setting of the outline properties.

Notes

To search for a member name containing $, you must precede it with three backslash (\) escape characters in the patternstring. For example, to search for member a$bc in Market, you must use IsMatch(Market.CurrentMember.MEMBER_NAME, "a\\\$bc").
To search for a character at the end of a line, you must precede the POSIX end-of-line anchor, which is a dollar sign ($), with one backslash (\) escape character in the patternstring. For example, to search for a member name that ends with a c in Market, you must use IsMatch(Market.CurrentMember.MEMBER_NAME, "c\$").
To search for any other special characters besides $, you must precede them with two backslash (\) escape characters in the patternstring. For example, to search for member a?bc in Market, you must use IsMatch(Market.CurrentMember.MEMBER_NAME, "a\\?bc").

Example

The following query searches for members whose names start with “new”:

SELECT
  Filter(Market.Levels(0).Members, 
   IsMatch(Market.CurrentMember.MEMBER_NAME, "^new")
  )
ON COLUMNS
FROM  Sample.Basic

The following query searches for members whose names start with at least an “n”:

SELECT
  Filter(Market.Levels(0).Members,
   ISMATCH(Market.CurrentMember.MEMBER_NAME, "^n+")
  ) 
ON COLUMNS
FROM  Sample.Basic

The following query searches for members whose names contain an “*”:

SELECT
  Filter(Year.Members,
   ISMATCH(Year.CurrentMember.MEMBER_NAME, "\\*")
  ) 
ON COLUMNS
FROM  Sample.Basic

The following query searches for members whose names contain zero or an “a”:

SELECT
  Filter(Year.Members,
   ISMATCH(Year.CurrentMember.MEMBER_NAME, "a?")
  ) 
ON COLUMNS
FROM  Sample.Basic