How to find a key pattern in multi-line text using regular expression

Question

I use regex to parse stored procedures to modify them. here is my sample text:

DECLARE @TempTable TABLE ( TempID int IDENTITY PRIMARY KEY, AMFID smallint, AppModID smallint, AppID tinyint, ModID smallint, FPID smallint, URL varchar(100), SortIndex smallint, [AppName] varchar(100), ModName varchar(100), FPName varchar(100), WUCData varchar(7000) ) -- Fill the temp table INSERT INTO @TempTable ( AMFID, AppModID, AppID, ModID, FPID, URL, SortIndex, [AppName], ModName, FPName, WUCData ) SELECT siAppModFP.AMFID, siAppModFP.AppModID, siAppModule.AppID, siAppModule.ModID, siAppModFP.FPID, siAppModFP.URL, siAppModFP.SortIndex, siApplication.[AppName], siModule.ModName, siFP.FPName, dbo.funcGetAppModFPWUC(siAppModFP.AMFID) FROM siApplication WITH (NOLOCK) 

...

I just want to get this part:

 DECLARE @TempTable TABLE ( TempID int IDENTITY PRIMARY KEY, AMFID smallint, AppModID smallint, AppID tinyint, ModID smallint, FPID smallint, URL varchar(100), SortIndex smallint, [AppName] varchar(100), ModName varchar(100), FPName varchar(100), WUCData varchar(7000) ) 

Please note that this may be repeated several times. I need all the declarations of temporary tables in the text. I used the following template:

 string re1 = "(Declare)( )(@)((?:[az][a-z0-9_]*))(\\s+)(Table)(\\s+)(\\(.*\\))"; Regex r = new Regex(re1, RegexOptions.Multiline | RegexOptions.IgnoreCase); 

But it does not work. Any ideas?

Answer 1

You must use singleline mode not multiline

Use this regex with singleline mode

 declare\s*@(?:[az]\w*)\s*table.*?\)\s*\) 

So it should be

 string re1 = @"declare\s*@(?:[az]\w*)\s*table.*?\)\s*\)"; Regex r = new Regex(re1 ,RegexOptions.Singleline | RegexOptions.IgnoreCase ); 

try here

Answer 2

Try Regex :

 DECLARE\ s+@ (\w+)\s+TABLE\s+\(([^,\(\)]+(\(\d+\))?(,)?)+\) 

and example code:

 var pattern = @"DECLARE\ s+@ (\w+)\s+TABLE\s+\(([^,\(\)]+(\(\d+\))?(,)?)+\)"; var matches = Regex.Matches(inputText, pattern); foreach(Match match in matches) Output.Lines.Add(match.ToString()); // or in LINQ way var result = from Match match in matches select match.ToString(); 

Answer 3

You need to get the balancing brackets

 Regex rx = new Regex(@"declare\s\@([a-zA-Z_][a-zA-Z0-9_]*)\w+table\w*\(((?<BR>\()|(?<-BR>\))|[^()]*)+\)");