Regular expressions is something native to .NET, yet not implemented in MS SQL, which I personally consider unfair. LIKE, CHARINDEX() and SUBSTRING() pale in comparison with regular expressions' functionality.

A Little Demo

Scalar functions

This query searches for lake and state/station etc inclusions, and displays the words it managed to find.

It returned full words Lake, Lakeshore, Lakewood, Lakeway and LakeLine — all match the lake[A-z]* pattern.

The stat[eion] pattern brought us State and Station.

Those were scalar functions.

Table Functions

Now it gets more interesting. The following example demonstrates a table function, which returns a result set of patterns matched, the index and the length of the matched word.

And here we see a RegExSplit by space.

Options

All functions accept a RegExOptions parameter, which is an int mask for a boolean set of these options:

• IgnoreCase
• Multiline
• ExplicitCapture
• Compiled
• Singleline
• IgnorePatternWhitespace
• RightToLeft
• ECMAScript
• CultureInvariant

I used the 1 in my examples: it stands for only IgnoreCase.

Complete list of functions

These functions use the Regex class from .NET.

Scalar functions:

1. RegExOptions — accepts a set of 0 and 1 values, each according to the options. Returns an integer value (the last, RegExOptions parameter of any other function)

2. RegExEscape — turns escape symbols like \ * + ? | { [ ( ) ^ $ . # <space> into searchable text symbols

3. RegExMatch — the string that matched the pattern

4. RegExIndex — integer of found match

5. RegExIsMatch — boolean fact of match found

6. RegExReplace — a string gone through plastic surgery

Table functions:

1. RegExSplit — returns a table with one column containing the split strings by a given pattern

2. RegExMatches — see demo above

Curiously, the table functions are implemented two-ways: for a row, and for a set: RegExSplit is paired with RegExSplitRow.

Installation

Feel like using it? Check this repo.