• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 31
  • Last Modified:

remove blocks text containing several lines in a text file

Hello,

In a text file, I search to remove blocks text containing several lines in a text file   like

create trigger [MSmerge%

    . . .
    . . . 

    return     

GO

Open in new window


Thanks

Regards
0
bibi92
Asked:
bibi92
  • 6
  • 5
1 Solution
 
oBdACommented:
That depends strongly on the input file.
In general, you need to read the file as a whole (Get-Content will by default return an array of strings).
Then you need to tell the RegEx to use Single-line mode; you do this by using inline options, namely "(?s)".
Line breaks can now be searched like any other character (using "\r\n").
You now need to be very careful with quantifiers, as they are greedy by default, so they will swallow as much as possible; use a "?" after the quantifier to make it lazy.
That said, try
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace '(?s)create trigger \[MSmerge%.*?\r\nGO', ''

Open in new window

And I've asked this before:
Please, add "Style Code" tags (from the tool bar above the Edit field) around any source you post; don't just put it in as regular text. This prevents unintended changes to the source, and makes copying and pasting it easier.
0
 
bibi92Author Commented:
Thanks but it doesn't work.
0
 
oBdACommented:
"it doesn't work" is usually not enough to help identifying what went wrong.
I saved this as C:\Temp\test.sql:
Foo
create trigger [MSmerge%

Make me disappear

    return     

GO
Bar

Open in new window

Then used this:
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$sql -replace '(?s)create trigger \[MSmerge%.*?\r\nGO', ''

Open in new window

And, as expected, had this as output:
Foo

Bar

Open in new window


If "it doesn't work" for you, you need to provide:
1. A sample input SQL file that can be used as is, without changes - enclosed in "Style Code" tags.
2. A description detailing exactly which line(s) from the code box need to be removed under which conditions.
0
Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

 
bibi92Author Commented:
Hello

Following un example but the text file can contain trigger for update :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [MSmerge_del_599C8427ACEF4B32984FAA8ED57D7B88] on [dbo].[T_repl] FOR DELETE   AS 
    declare @is_mergeagent bit, @at_publisher bit, @retcode smallint 

    set rowcount 0
    set transaction isolation level read committed

            select @is_mergeagent = convert(bit, sessionproperty('replication_agent'))
            select @at_publisher = 0 
    if (select trigger_nestlevel()) = 1 and @is_mergeagent = 1
        return 
    declare @article_rows_deleted int
    select @article_rows_deleted = count(*) from deleted
    if @article_rows_deleted=0
        return
    declare @tablenick int, @replnick binary(6), 
            @lineage varbinary(311), @newgen bigint, @oldmaxversion int, @child_newgen bigint, 
            @child_oldmaxversion int, @child_metadatarows_updated int, @cv varbinary(1),
            @logical_record_parent_oldmaxversion int, @logical_record_lineage varbinary(311), @logical_record_parent_regular_lineage varbinary(311), @logical_record_parent_gencur bigint,
            @num_parent_rows int, @logical_record_parent_rowguid uniqueidentifier, @parent_row_inserted bit, @rowguid uniqueidentifier 
    declare @dt datetime, @nickbin varbinary(8), @error int
     
    set nocount on
    select @tablenick = 8117001    
    if @article_rows_deleted = 1 select @rowguid = rowguidcol from deleted
    select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick
    select @dt = getdate()

    select @replnick = 0x7b8fe113ebeb
    set @nickbin= @replnick + 0xFF

    select @newgen = NULL
        select top 1 @newgen = generation from [dbo].[MSmerge_genvw_599C8427ACEF4B32984FAA8ED57D7B88] with (rowlock, updlock, readpast) 
        where art_nick = 8117001      and genstatus = 0    
        
            and  changecount <= (1000 - isnull(@article_rows_deleted,0))
    if @newgen is NULL
    begin
        insert into [dbo].[MSmerge_genvw_599C8427ACEF4B32984FAA8ED57D7B88]  with (rowlock)
            (guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
               values (newid(), 0, @tablenick, @nickbin, @dt, @article_rows_deleted)
        select @error = @@error, @newgen = @@identity    
        if @error<>0 or @newgen is NULL
            goto FAILURE
    end
    else
    begin
        -- now update the changecount of the generation we go to reflect the number of rows we put in this generation
        update [dbo].[MSmerge_genvw_599C8427ACEF4B32984FAA8ED57D7B88]  with (rowlock)
            set changecount = changecount + @article_rows_deleted
            where generation = @newgen
        if @@error<>0 goto FAILURE
    end
  
    set @lineage = { fn UPDATELINEAGE(0x0, @replnick, @oldmaxversion+1) }  
    if @article_rows_deleted = 1
        insert into [dbo].[MSmerge_tsvw_599C8427ACEF4B32984FAA8ED57D7B88] with (rowlock) (rowguid, tablenick, type, lineage, generation)
            select @rowguid, @tablenick, 1, isnull((select { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @replnick, @oldmaxversion+1) } from 
            [dbo].[MSmerge_ctsv_599C8427ACEF4B32984FAA8ED57D7B88] c with (rowlock) where c.tablenick = @tablenick and c.rowguid = @rowguid),@lineage), @newgen
    else
        insert into [dbo].[MSmerge_tsvw_599C8427ACEF4B32984FAA8ED57D7B88] with (rowlock) (rowguid, tablenick, type, lineage, generation)
            select d.rowguidcol, @tablenick, 1, { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @replnick, @oldmaxversion+1) }, @newgen from 
            deleted d left outer join [dbo].[MSmerge_ctsv_599C8427ACEF4B32984FAA8ED57D7B88] c with (rowlock) on c.tablenick = @tablenick and c.rowguid = d.rowguidcol 
             
    if @@error <> 0
        GOTO FAILURE  
        delete [dbo].[MSmerge_ctsv_599C8427ACEF4B32984FAA8ED57D7B88]  with (rowlock)
        from deleted d, [dbo].[MSmerge_ctsv_599C8427ACEF4B32984FAA8ED57D7B88] cont with (rowlock)
        where cont.tablenick = @tablenick and cont.rowguid = d.rowguidcol
        option (force order, loop join)

    if @@error <> 0
        GOTO FAILURE

    
    return
FAILURE:
    if @@trancount > 0
        rollback tran
    raiserror (20041, 16, -1)
    return  

GO

Open in new window


Thanks
0
 
oBdACommented:
Okay, that's part 1.
But, sorry, "but the text file can contain trigger for update" does not qualify as "detailing exactly which line(s) from the code box need to be removed under which conditions."
The part beginning and including line 5 up until and including line 86 can be removed by simply changing the % to an underscore:
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$sql -replace '(?s)create trigger \[MSmerge_.*?\r\nGO', ''

Open in new window

0
 
bibi92Author Commented:
Hello

I need to remove all text block following :

create trigger [MSmerge% ...
.....
.....
....
GO

Open in new window


I have tried $sql -replace '(?s)create trigger \[MSmerge_.*?\r\nGO', ''

The replace is not done. I try with  Out-File "C:\Temp\test.sql"
Thanks
0
 
oBdACommented:
Could be an issue with line endings. Could you please attach the file you're using for testing using the "Attach file" link below the Edit field?
0
 
bibi92Author Commented:
Hello,

Maybe
In attachment, the file.
I try to remove too
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Thanks
trigger_msmerge.txt
0
 
oBdACommented:
The line endings in the uploaded file are what's expected on a Windows system, that is, CRLF.
The script worked just fine here, except for the trigger starting in line 227. That one is skipped because the name is not enclosed in square brackets.
This now eradicates everything from the input file, because it only contains "create trigger" statements; don't know if that's intentional, and the full file has additional content.
Note that for easier testing, there's currently no export to an output file, just the pipeline/console output:
$Sql = [IO.File]::ReadAllText("C:\Temp\trigger_msmerge.txt")
$sql -replace '(?s)SET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO(\r\n)+create trigger \[?MSmerge_.*?(\r\n)+GO(\r\n)?', ''

Open in new window

0
 
Bill PrewCommented:
This seems to get them, give this a try.  It will display the results to the screen for testing.

$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace '(?s)create trigger \[MSmerge.*?\nGO', ''

Open in new window

~bp
0
 
oBdACommented:
This now handles both CRLF as well as single LF as EOL, just in case:
$Sql = [IO.File]::ReadAllText("C:\Temp\trigger_msmerge.txt")
$Sql -replace '(?s)SET ANSI_NULLS ON\r?\nGO\r?\nSET QUOTED_IDENTIFIER ON\r?\nGO(\r?\n)+create trigger \[?MSmerge_.*?(\r?\n)+GO(\r?\n)?', ''

Open in new window

0
 
bibi92Author Commented:
Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now