Regular expression Replace and Match used in managed sql





Every one knows the power of .net clr (Dot Net Common Language Runtime).  Following are are two functions which are being used by me in sql server 2005.

RegexMatch function returns the true false (0/1); it takes two parameters input as characters or string and Regular expression to match.

RegexRpelace function replaces the carachters passed as regular experisson in the sql, it takes three arguments. input charcters or string  , Reqular expression pattern and replacement character. Both function are written in c# you can convert them to vb.net if required.  These functions must be used in managed assemblies.
//class vriable

public static readonly RegexOptions Options = RegexOptions .IgnorePatternWhitespace | RegexOptions.Singleline;

[SqlFunction]
public static SqlBoolean RegexMatch(SqlChars input, SqlStringpattern){

Regex regex = new Regex(pattern.Value, Options);

return regex.IsMatch(new string(input.Value));

}

[SqlFunction]

public static SqlString RegexReplace(SqlString input, SqlString pattern,SqlStringreplacementcharacter){

Regex regex = new Regex (pattern.Value, Options);

return regex.Replace(input.ToString() ,replacementcharacter.ToString() );

}

How to use this in sql query

select dbo.RegexReplace(organisationname,‘[\d\.\-\&\(\)\£\^\+\@\/\''\#\~\!\,\>\<\=\`\$\\*\%\\\:\;\¬\}\{\]\[ ]‘,) from organisation.
Above sql query which uses RegexReplace function of managed sql is taking threee parametres, organiationname as input, regular expression in red and ” as replacement.

In organisation name find all characters which are defined in reqular expression and replace them with zero string.




Tags: , , ,

Leave a Reply