Regular Expression with find a pattern and replace

[shashi bhosale]

Looking to find a block where starts with CREATE procedure and ends with ) as
and In replace add additional line select @site = ‘Site’ at the end.

CREATE PROCEDURE [dbo].[ki_APSGetTopLevelDemand_BKUP]
(
@IpRefType AS VARCHAR(100),
@IpRefNum AS VARCHAR(100),
@IpRefLine AS INT
)
AS

Its not case sensitive.

[PeterJones]

@shashi-bhosale ,

When I read your post earlier this week, I had hoped someone else would chime in, because I was pretty busy. Sorry no one else had a chance to reply before I finally found a spare moment to go back through unanswered questions.

If I have interpreted your request, then if I start with

CREATE PROCEDURE [dbo].[ki_APSGetTopLevelDemand_BKUP]
(
@IpRefType AS VARCHAR(100),
@IpRefNum AS VARCHAR(100),
@IpRefLine AS INT
)
AS

blah blah blah

CREATE PROCEDURE [dbo].[ki_APSGetTopLevelDemand_BKUP]
(
@IpRefType AS VARCHAR(100),
@IpRefNum AS VARCHAR(100),
@IpRefLine AS INT
)
AS

and use

• FIND = (\)\s*AS)
• REPLACE = $0\r\n@site = 'Site'
• SEARCH MODE = regular expression

After doing the two replacements (or a REPLACE ALL), I get what I think you want:

CREATE PROCEDURE [dbo].[ki_APSGetTopLevelDemand_BKUP]
(
@IpRefType AS VARCHAR(100),
@IpRefNum AS VARCHAR(100),
@IpRefLine AS INT
)
AS
@site = 'Site'

blah blah blah

CREATE PROCEDURE [dbo].[ki_APSGetTopLevelDemand_BKUP]
(
@IpRefType AS VARCHAR(100),
@IpRefNum AS VARCHAR(100),
@IpRefLine AS INT
)
AS
@site = 'Site'

If you want to know more about the individual tokens in my search and replace, look at the official search/regex section of the usermanual, linked below, for the following concepts

token	see usermanual section about
parentheses	capture groups
\s	character escape sequences
*	multiplying operators
\r\n	control characters (note that they are case sensitive)

-—

Useful References

• Please Read Before Posting
• Template for Search/Replace Questions
• FAQ: Where to find regular expressions (regex) documentation
• Notepad++ Online User Manual: Searching/Regex