Spreadsheet Formulas - Regular Expression Functions

Statistica provides a broad selection of regular expression functions that can be used in spreadsheet formulas like all other functions. Regular expression functions provide the ability to search and replace text using "fuzzy" matching.

Below is a list of all available regular expression functions.

Name

Parameters

Explanation

RE_SEARCH

(input_expr, reg_exp, options)

Returns true if input_expr contains a substring that matches the reg_exp. See below of an explanation of the options available for the third parameter.

RE_MATCH

(input_expr, reg_exp, options)

Returns true if input_expr matches the regular expression reg_exp. See below of an explanation of the options available for the third parameter.

RE_REPLACE

(input_expr, reg_exp, replace_string, options)

Replaces portions of input_expr that match reg_exp with replace_string. See below of an explanation of the options available for the third parameter.

LIKE

LIKE is an operator that takes one compares the L-value and R-value surrounding it. This is to say, it's usage is such: x LIKE Y

Typically x is a text variable number or name and y is a string constant.

 

LIKE is similar to SQL Server's LIKE operator:

% matches any string of 0 or more characters.

_ matches any single character.

[] matches any single character in a range or set of characters (e.g. [a-f], [abcdef]).

[^] matches any single character not in a range or set of characters (e.g. [^a-f], [^abcdef]).

 

Note that literal %, _, etc must be escaped with \. Literal \ should be escaped as \\.

 

LIKE is always case insensitive.

The following options are available for RE_SEARCH, RE_MATCH, and RE_REPLACE:

b: Use POSIX basic grammar rather than PERL-like grammar. Similar to sed and grep.

x: Use POSIX extended grammar (similar to egrep).
Note that b and x are mutually exclusive; x overrides b.

i: Ignore case.

e: Allow the pattern to match an empty string.

s: Allow '.' to match newlines (similar to PERL's /s).

m: Allow the use of ^, $ as anchors to match start and end of a line, respectively (similar to PERL's /m).

g: Replace globally (similar to PERL's /g). In a RE_REPLACE, replaces all matches not just the first.

c: In a RE_REPLACE, do not copy to the output string portions of the input that did not match the regular expression.

See also: Regular Expression Examples, Spreadsheet Formulas - Overview, Spreadsheet Formulas - Syntax Summary, Spreadsheet Formulas - Examples, Spreadsheet Formulas - Predefined Functions.