Regex: match SQL PRINT blocks with quoted text in it

Question

Hi all,

I got the following text:

PRINT CONVERT(NVARCHAR, CURRENT_TIMESTAMP, 111) + ' ' + CONVERT(NVARCHAR, CURRENT_TIMESTAMP, 108) + ' -Test Mode : ' + (CASE WHEN @turbo_mode_ind = 1 THEN 'some text ''test'' some more text.' ELSE 'and even more text ''temp'' when will it stop ?' END)

PRINT 'text don''t text'

PRINT 'text ''test2'' text'

and what I want to match is:

PRINT CONVERT(NVARCHAR, CURRENT_TIMESTAMP, 111) + ' ' + CONVERT(NVARCHAR, CURRENT_TIMESTAMP, 108) + ' -Test Mode : ' + (CASE WHEN @turbo_mode_ind = 1 THEN 'some text ''test''

PRINT 'text ''test2''

So basically I want to match:

• starting at PRINT
• each char that comes after PRINT (.*)
• inclusive line-breaks (don't stop at line-breaks)
• with \'{2}\w+\'{2} at the end of the match
• non-greedy (.*?)
• AND no empty line(s) between PRINT and \'{2}\w+\'{2}

I've already compsed this, but it still matches empty line(s):

PRINT.?\'{2}\w+\'{2}(?!\n\s\n)

Thx for the help

Answer 1

Edit after comment:

Looking at the requirements again I could not come up with a single regex solution quickly. In your comments you mention that you're using C#.

A possible solution would therfore be to first split the string at blank lines and then extracting the text.

Something like this:

string pattern = @"^$";

foreach (string result in Regex.Split(input, pattern, RegexOptions.Multiline) 
{
    Regex rxFindSql = Regex(@"PRINT.*?\'{2}\w+?\'{2}", RegexOptions.SingleLine)       
    MatchCollection matches = rxFindSql.Matches(result);
}

This should do the trick but I did not test the code.

I hope this helps.

Answer 2

Yep, I also came to the conclusion that it probably won't work in one RegEx so I've used your code and guess what ? It works perfectly :)

Thx for the aid !