regular expression match doesn't return parenthesis 1

[jubble]

I'm trying to remove CREATE PROCEDURE commands from a script using system.text.regularexpressions

Basically every instance of CREATE PROCEDURE through to GO needs to be removed

The regular expression '(CREATE PROCEDURE [^(?:\bGO\b)]+)' finds that string but the string RETURNED stops at the first parenthesis '(' so if I try the replace function it only removes 'CREATE PROCEDURE WC_FindMain '

What's going on? Am I doing something duff or is there an issue with returning reserved characters?

Dim l_sArchiveScript As String
Dim l_oTextReader As IO.StreamReader

l_oTextReader = New IO.StreamReader("c:\temp\testregexpression.txt")

l_sArchiveScript = l_oTextReader.ReadToEnd
l_oTextReader.Close()

Dim l_osqlStatements As System.Text.RegularExpressions.MatchCollection

l_osqlStatements = System.Text.RegularExpressions.Regex.Matches(l_sArchiveScript, "(CREATE PROCEDURE [^(?:\bGO\b)]+)")

MsgBox(l_osqlStatements(0).Value)


Here's an example of the script:

CREATE PROCEDURE WC_UpdateData (@Value decimal(28,6) , @OpBal decimal(28,6), @DataId as integer )
AS
Update Data
Set Value = Value + @Value,
OpBal = OpBal + @OpBal
Where DataId = @DataId


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE WC_UpdateDataSubmitted (@Value decimal(28,6) , @OpBal decimal(28,6), @DataId as integer,@ParentMainID as integer )
AS
Update DataSubmitted
Set Value = Value + @Value,
OpBal = OpBal + @OpBal,
ParentMainId = @ParentMainId
Where SubmitDataID = @DataId


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[Custom24]

Try this

CREATE\sPROCEDURE([\n\r]|.)+?GO

HTH

Mark

 

[jubble]

Perfect!

Is there a good reference out there for regular expression syntax for VB.Net?

I've got some documentation from MSDN but it makes no mention of '\s' and '\r' syntax which you used. (got a vague idea what the \s might be for ;-) )

Have tried Google but nothing definitive.

Thank for saving me a few clumps of hair.

 

[Custom24]

My reference is Chapter 12 of Francesco Balena's excellent Programming Visual Basic .net

Otherwise, the MSDN documentation which comes with Visual Studio has a pretty good section on regular expressions.



Mark