CODEWITHSUNDEEP
regexvisual-studio-2012
test-in-prod
test-in-prod

Reputation: 498

Visual Studio Regex find certain words

I am trying to use Visual Studio's Regex find in files. Basically, I have a database project that contains some .sql files which then contain CREATE PROCEDURE blocks.

I am looking to use a form of regular expression to only find .sql files that contain both "RAISERROR" and "COMMIT" statements in them. Unforunately, (?=\bRAISERROR\b)(?=\bCOMMIT\b) does not seem to find anything and any other variations seem to only retrieve either or as the result. I am not strong in regular expressions and I was unable to find a pattern that could perhaps possibly help with this.

Here's a sample procedure that should match that I am testing with

CREATE PROCEDURE [procedurename]

<irrelevant parameters>

AS
<TSQL code>

BEGIN TRANSACTION

<TSQL CODE>

RAISERROR('message', 16, 1)

    <TSQL code>

RAISERROR('another message', 16, 2)

    <TSQL code>

COMMIT

RETURN 0

Upvotes: 0

Views: 237

Answers (1)

Taemyr
Taemyr

Reputation: 3437

Your attempt would not find anything. It looks for a zero length string that is immidately preceded by both RAISERROR and COMMIT. No such string can exist.

Instead you should search for RAISERROR which is followed at a later stage by COMMIT

(?s)(\bRAISERROR\b.*\bCOMMIT\b|\bCOMMIT\b.*\bRAISERROR\b)

Explanation of the regexp; (?s) is the flag for the rather misnamed "single line" mode, it forces . to match newlines. Then find the word "RAISEERROR" followed by an arbitrary number of arbitrary characters followed by "COMMIT". Or the same but in opposite order.

Upvotes: 1

Related Questions