Enhanced find and replace function...

Question

Post reply

Enhanced find and replace function...

Jake Shelton

Hall of Fame

Points: 3109
More actions
April 17, 2014 at 10:33 am

#290406

Hi all,
I'm looking for a way to pull off a complex find+replace within some code, as follows:
@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'
We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...
Rules:
1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).
2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:\MyFileLocationFolderHere" (this folder remains constant).
Cheers,
Jake

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i assume the string can be anything, and not justa ~33 character GUID, right?

i think the right thing to do is to find find where the slash starts, and where the single quote after it ends.

here's my first crack at the problem; i'm just using a CTE to make the start and end points easier to find:

DECLARE @SearchTerm VARCHAR(200)

SET @SearchTerm = 'N''DTSRun /'

PRINT @SearchTerm

;WITH MyCTE([SampleData])

AS

(

SELECT '@step_name = N''SAME - OCF Collins (Tabard)'', @command = N''DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC '', @database_name = N''' UNION ALL

SELECT '@step_name = N''SAME - OCF Collins (Tabard)'', @command = N''DTSRun /LowellFile '', @database_name = N'''

), Locations

AS

(

SELECT

CHARINDEX( @SearchTerm,[SampleData]) + 10 As StartSpot, --+10 is the length of "N'DTSRun /"

CHARINDEX('''',[SampleData],CHARINDEX(@SearchTerm,[SampleData]) + 10) As EndSpot,

*

FROM MyCTE

)

SELECT STUFF([SampleData], StartSpot,EndSpot - StartSpot,'D:\MyFileLocationFolderHere' ),*

FROM Locations

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 2

Hi Lowell, many thanks for that! I haven't run it yet, but the GUID is actually double or triple that length, I had to shorten it for readability purposes.