ignore case with regular expression

Question

I have created a CLR in a SQL server 2005 database. It is a simple regular expression user defined function that will check if a pattern exists in the string I pass into the function.

The dot net code I use in the CLR is shown below:

Return System.Text.RegularExpressions.Regex.IsMatch("Input", "pattern")

This returns a bit value of 1 if a match is found.

The pattern I am using is

 +(Create|Alter) +(Proc|Procedure) +

What I want this to do is find any cases of “create or alter” “procedure or proc” regardless of the case. How can I get the expression to ignore case?

I have tried

/ +(Create|Alter) +(Proc|Procedure) +/i

but this doesn’t work.

EDIT: I have looked around on the internet and used various suggestions. None of which have worked or I have done them wrong. If someone could give me a pattern that will ignore the case that would be much appreciated!

Answer: What i was trying to achieve was a regular expression that ignores case. Dot Net does have parameters that can be passed in to set ignore case however it being in a CLR means i do not have the ability to pass the parameters in to the function.

The pattern that achieves this is : (?i) +(Create|Alter) +(Proc|Procedure) +

Answer 1

You can use the inline "case-insensitive" modifier (?i):

(?i) +(Create|Alter) +(Proc|Procedure) +

Answer 2

Try using:

Regex regex = new Regex(
    regexStringHere,
    RegexOptions.IgnoreCase);

return regex.IsMatch(inputStringHere);

Hope this helps.

Answer 3

try this.

Return System.Text.RegularExpressions.Regex.IsMatch("input", @".*(Create|Alter).+(Proc|Procedure).+", RegexOptions.IgnoreCase);

I would recommend installing free software Expresso to test your regular expressions and generate .Net / c# code for search, replace, etc