Finding or replacing text in SQL is a very frequent scenario. “LIKE” and “PATHINDEX” are often used but, unfortunately, are not close to be as much powerful and offering the same possibilities as regular expression (Regex) does.
Eval SQL.NET lets you use and exploit fully C# regular expression features directly in T-SQL stored procedures, functions and triggers. It’s possible to use regex in SQL search condition and select statement.
SQL Regex - IsMatch
You need to perform a rule validation or search with a condition to find valid/invalid rows but “LIKE” and “PATHINDEX” limitations don’t cover your requirements:
Find customer with invalid email
Find customer with invalid phone
Find customer with invalid website
SQL Regex IsMatch indicates whether the regular expression finds a match in the string or not.
SQL Regex - Match
You need to extract the first occurrence from a string such as user profile description:
Extract the first email
Extract the first phone
Extract the first website
SQL Regex Match searches in a string for the first occurrence of the regular expression and returns the match.
SQL Regex - Matches
You need to extract all occurrences from a string such as blog post:
Extract all emails
Extract all phones
Extract all websites
SQL Regex Matches searches in the string for all occurrences of the regular expression and returns all the matches.
SQL Regex - Replace
You need to convert, remove or substitute a text with a specific format:
Convert plain url to html link
Substitute name in text template
SQL Regex Replace searches for strings that match a regular expression pattern and replaces a value with a replacement string.
SQL Regex - Split
You need to split a string but the traditional “fn_split” method is limited and doesn’t cover your scenario:
Use multiple delimiter
Keep the delimiter in value
Split the first X occurrences
SQL Regex Split lets you split a string into an array of substrings using a regular expression.
If you are currently using an fn_split User-Defined Functions (UDF), this benchmark will probably make change your mind!
Eval SQL.NET offers all advanced C# regular expression features in T-SQL statements and search conditions. Even more, you can use C# syntax to return from SQL what you really want and not only the regex result.
While it should never replace scenarios where pure SQL operators/functions such as “LIKE” and “PATHINDEX” are sufficient, Eval SQL.NET will help you easily cover all unsupported ones.