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