0

I need to perform a search and replace operation on hundreds of SQL script files. The idea is to transform this:

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* comments xxxx */
ALTER PROCEDURE ....
    BEGIN
        ...
    END

Into this:

ALTER PROCEDURE ....
    BEGIN
        ...
    END

To do this I would use Notepad++. I want to remove anything before ALTER PROCEDURE. How can I achieve that?

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
ab_732
  • 3,639
  • 6
  • 45
  • 61

1 Answers1

1

Use the following with . matches newline:

Find what:    USE\s+\[[^\]]*?\].*?(ALTER\s+PROCEDURE.*?\bEND\b)
Replace with: $1

\[[^\]]*?\] will make sure you will match any characters from [ up to ] after USE.

EDIT: If you plan to just remove everything from the beginning to the last occurrence of ALTER, you may use .+(?=ALTER) regex in the Find what field, and replace with nothing (empty string).

Settings:

enter image description here

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • my target is to remove anything before "ALTER". maybe something simpler? – ab_732 Apr 27 '15 at 12:24
  • Everything? Then `.*?(?=ALTER)`. And replace with nothing. – Wiktor Stribiżew Apr 27 '15 at 12:25
  • This find the occurrence, it tells me that one replacement has been made but it actually does not remove anything – ab_732 Apr 27 '15 at 12:28
  • I have tried this `.+(?=ALTER)` now, and it matches up to the last `ALTER`. Is that waht you need? You can use `.+?(?=ALTER)` to match just blocks between `ALTER`s. I think you need something like `USE .+?(?=\bALTER\b.*\bEND\b)` and replace with empty string. – Wiktor Stribiżew Apr 27 '15 at 12:28
  • Aaah it works! Sorry, silly me. I did not select the "matches newline" option. – ab_732 Apr 27 '15 at 12:38