RegEx delete between text and end of line

[maniootek]

I am trying to delete comments from my sql file but I am not sure how to do it with RegEx. SQL files contains comments which starts from "--" character untill end of line

$SQL_File = FileOpen("query.sql")

$SQL_Query = FileRead($SQL_File)

$SQL_Query_without_comments = StringRegExpReplace($SQL_Query,"(--).*(\n)","")

this code does not delete comments, any idea?

[jguinch]

Try this :

$SQL_Query_without_comments = StringRegExpReplace($SQL_Query,"(?m)^--\N*","")

 

[maniootek]

does not work

please note that "query.sql" looks like this:

 

[Dgameman1]

Would you be able to copy and paste the query.sql file instead of taking a picture?

[jguinch]

it works for me.

$SQL_File = FileOpen("file.sql")

$SQL_Query = FileRead($SQL_File)

$SQL_Query_without_comments = StringRegExpReplace($SQL_Query,"(?m)^--\N*","")

$hFile = FileOpen("file2.sql", 2)
FileWrite($hFile, $SQL_Query_without_comments)
FileClose($hFile)

file2.sql has no comment.

Edited March 17, 2016 by jguinch

[maniootek]

yes, it works but only when line starts from "--" text, but how to make it work also when comment and sql code comes in the same line? example:
 

Quote

 

testcode testcode

testcode testcode --comment

testcode testcode

 

 

[jguinch]

And what happen if you have a string with "--" ?

INSERT INTO `table` (`ID`, `NAME`, `TEXT`) VALUES
(1, 'TheName', 'Some text -- and some text', ''); -- comment

 

[Dgameman1]

6 minutes ago, maniootek said:

yes, it works but only when line starts from "--" text, but how to make it work also when comment and sql code comes in the same line? example:
 

 

$SQL_Query_without_comments = StringRegExpReplace($SQL_Query, "--(.*?)(\N*)", "")

 

Edited March 17, 2016 by Dgameman1

[maniootek]

now it works, thank you

one day i must learn more about regex, it is very powerfull option

[maniootek]

everything is almost perfect.

any idea how to change regexp pattern to achieve this (with one regex pattern)

here is code sample:

$SQL_QUERY = "" & _
"-- comment 1" & @CRLF & _
"-- comment 2" & @CRLF & _
"" & @CRLF & _
"-- comment 3" & @CRLF & _
"" & @CRLF & _
"sqlcodeline1 -- comment 4" & @CRLF & _
"sqlcodeline2" & @CRLF & _
"sqlcodeline3"
msgbox(0,0,$SQL_QUERY)
$newstring = StringRegExpReplace($SQL_Query, "--(.*?)(\N*)", "")
while stringinstr($newstring, @CRLF & @CRLF)
    $newstring = StringReplace($newstring, @CRLF & @CRLF, @CRLF) ;how to avoid this line and include it into above regex pattern?
WEnd
msgbox(0,0,$newstring)

 

[InunoTaishou]

$SQL_QUERY = "-- comment 1" & @CR & _
        "-- comment 2" & @LF & _
        "" & @CRLF & _
        "-- comment 3" & @LF & _
        "" & @CRLF & _
        "" & @CRLF & _
        "" & @LF & _
        "sqlcodeline1 -- comment 4" & @CR & _
        "sqlcodeline2" & @CRLF & _
        "sqlcodeline3"

Global $sCleanQuery = StringRegExpReplace($SQL_QUERY, "(--.*(\r|\n){1,})", @CRLF)
ConsoleWrite($sCleanQuery& @CRLF)

I'm sure there's a regex out there for removing the empty lines but this will remove the comments, regardless of where they're at.

[jchd]

This will do, but if -- occurs within the SQL statement, then it will explode in flight.

Local $SQL_QUERY = "-- comment 1" & @CR & _
        "-- comment 2" & @LF & _
        "" & @CRLF & _
        "                        -- comment 3" & @LF & _
        "" & @CRLF & _
        "" & @CRLF & _
        "" & @LF & _
        "sqlcodeline1 -- comment 4" & @CR & _
        "sqlcodeline2" & @CRLF & _
        "sqlcodeline3"

Local $sCleanQuery = StringRegExpReplace($SQL_QUERY, "(?m)^\s*--.*\R|^\R|--.*", "")
ConsoleWrite($sCleanQuery& @CRLF)

Note that /* ... */ style SQL comments are ignored here.