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)

----

Regular Expression with find a pattern and replace

Useful References