SQL Eval Function Regex_SplitOp
Regex_SplitOp
splits an input string into an array of substrings at the positions defined by a regular expression pattern using the specified matching options.
Regex_SplitOp ( @input NVARCHAR (MAX), @pattern NVARCHAR (MAX), @options INT ) RETURNS TABLE (Match NVARCHAR (MAX) NULL)
Parameters
- input: The input string that contains the text to convert.
- pattern: The regular expression pattern to match.
- options: A bitwise combination of the enumeration values that provide options for matching.
Options
An array of strings.
Options | Integer Value | Descritpiton |
---|---|---|
None | 0 | Specifies that no options are set. |
IgnoreCase | 1 | Specifies case-insensitive matching. |
Multiline | 2 | Multiline mode. Changes the meaning of ^ and $ so they match at the beginning and end, respectively, of any line, and not just the beginning and end of the entire string. |
ExplicitCapture | 4 | Specifies that the only valid captures are explicitly named or numbered groups of the form (? |
Compiled | 8 | Specifies that the regular expression is compiled to an assembly. This yields faster execution but increases startup time. |
Singleline | 16 | Specifies single-line mode. Changes the meaning of the dot (.) so it matches every character (instead of every character except \n). |
IgnorePatternWhitespace | 32 | Eliminates unescaped whitespace from the pattern and enables comments marked with #. |
RightToLeft | 64 | Specifies that the search will be from right to left instead of from left to right. |
ECMAScript | 256 | Enables ECMAScript-compliant behavior for the expression. This value can be used only in conjunction with the IgnoreCase , Multiline , and Compiled values. The use of this value with any other values results in an exception. |
CultureInvariant | 512 | Specifies that cultural differences in language are ignored. |
You can also use more than one option by specifying the sum of their integer values. For example, to specify IgnoreCase
and Multiline
options, use 3 integer value and pass it as 3rd parameter.
Returns
Gets the captured substring from the input string.
Example
SELECT * FROM Regex_Split('plum--pear', '-', 1)
Regex_SplitOp4k
It is equivalent to Regex_SplitOp
except no NVARCHAR(MAX) parameters; it can be used when input data will never be over 4000 characters as this function offers better performance.
Regex_SplitOp4k ( @input NVARCHAR (4000), @pattern NVARCHAR (4000), @options INT ) RETURNS TABLE (Match NVARCHAR (MAX) NULL)
Example
SELECT * FROM Regex_Split4k('plum--pear', '-', 1)