Can notepad++ replace text based on other text in the same file using regex?

I would like to replace 'HCBANK' to the name of the table in CREATE TABLE line.

All files have 'HCBANK' at the same place.

Example

File A.sql

IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[HCBANK]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [dbo].[AGEGROUP](...

File B.sql

IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[HCBANK]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [dbo].[HCBLACKLIST](...

and so on.

Expected result:

File A.sql

IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[AGEGROUP]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [dbo].[AGEGROUP](...

File B.sql

IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[HCBLACKLIST]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [dbo].[HCBLACKLIST](...

I manage to capture the correct table name using

\]\.\[(.*?)\]

But, not sure how to replace the HCBANK using Find In Files (Ctrl+Shift+F) - since I have lot of files.

How do I solve this problem?

2 Answers

1
Toto On Best Solutions
  • Ctrl+H
  • Find what: IF NOT EXISTS \(SELECT \* FROM dbo\.sysobjects where id = object_id\(N'dbo\.\[\K[^]]+(?=]'\) and.+?CREATE TABLE \[[^]]+\]\.\[([^]]+)\])
  • Replace with: $1
  • check Wrap around
  • check Regular expression
  • CHECK . matches newline
  • Replace all

Explanation:

IF NOT EXISTS \(SELECT \* FROM dbo\.sysobjects where id = object_id\(N'dbo\.\[  # literally
\K                                          # forget all we have seen until this position
[^]]+                                       # 1 or more non ]
(?=                                         # start lookahead
  ]'\) and.+?CREATE TABLE \[[^]]+\]\.\[     # literally
  ([^]]+)                                   # group 1, 1 or more non ]
  \]                                        # ]
)                                           # end lookahead

Screen capture:

enter image description here

1
The fourth bird On

You could use 3 capturing groups and refer to those in the replacement.

This could be an example to match the first part of the query in group 21until HCBANK and then match the rest after it in group 2.

You could make the match as detailed as you want, but this would be the idea of the find and replace.

(IF NOT EXISTS \(SELECT \* FROM dbo\.sysobjects where id = object_id\(N'dbo\.\[)[^]]+(]'\) and.*(?:\r?\n|\r)BEGIN(?:\r?\n|\r)CREATE TABLE \[[^]]+\]\.\[([^]]+)\])

In the replacement use $1$3$2

Regex demo