Last active
June 23, 2017 09:52
-
-
Save fredtux/7189043737df5af25a9a to your computer and use it in GitHub Desktop.
Regex CLR
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using System; | |
| using System.Data; | |
| using System.Data.SqlClient; | |
| using System.Data.SqlTypes; | |
| using Microsoft.SqlServer.Server; | |
| using System.Text.RegularExpressions; | |
| public partial class UserDefinedFunctions { | |
| [Microsoft.SqlServer.Server.SqlFunction] | |
| public static SqlBoolean RegexMatch(string inputValue, string regexPattern) { | |
| if (string.IsNullOrEmpty(inputValue) || string.IsNullOrEmpty(regexPattern)) | |
| return false; | |
| return Regex.IsMatch(inputValue.TrimEnd(null), regexPattern.TrimEnd(null)); | |
| } | |
| [Microsoft.SqlServer.Server.SqlFunction] | |
| public static SqlString RegexReplace(string inputValue, string regexPattern, string replaceValue) { | |
| if (string.IsNullOrEmpty(inputValue) || | |
| string.IsNullOrEmpty(regexPattern) || | |
| string.IsNullOrEmpty(replaceValue)) { | |
| return ""; | |
| } | |
| return Regex.Replace(inputValue, regexPattern, replaceValue); | |
| } | |
| [Microsoft.SqlServer.Server.SqlFunction] | |
| public static SqlString RegexGroupExtract(string inputValue, string regexPattern, int groupNumber) { | |
| if (string.IsNullOrEmpty(inputValue) || | |
| string.IsNullOrEmpty(regexPattern) || | |
| groupNumber < 0) { | |
| return ""; | |
| } | |
| return Regex.Match(inputValue, regexPattern).Groups[groupNumber].Value; | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE ASSEMBLY SqlRegularExpressions | |
| -- Replace path | |
| from 'C:\Path\To\RegularExpressions.dll' | |
| WITH PERMISSION_SET = SAFE | |
| GO | |
| CREATE FUNCTION RegexMatch(@inputValue nvarchar(max), @regexPattern nvarchar(max)) RETURNS BIT | |
| AS EXTERNAL NAME SqlRegularExpressions.UserDefinedFunctions.RegexMatch | |
| GO | |
| CREATE FUNCTION RegexReplace(@inputValue nvarchar(max), @regexPattern nvarchar(max), @replaceValue nvarchar(max)) | |
| RETURNS nvarchar(max) | |
| AS EXTERNAL NAME SqlRegularExpressions.UserDefinedFunctions.RegexReplace | |
| GO | |
| CREATE FUNCTION RegexGroupExtract(@inputValue nvarchar(max), @regexPattern nvarchar(max), @groupNumber int) | |
| RETURNS nvarchar(max) | |
| AS EXTERNAL NAME SqlRegularExpressions.UserDefinedFunctions.RegexGroupExtract | |
| GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment