maniootek Posted March 17, 2016 Share Posted March 17, 2016 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? Link to comment Share on other sites More sharing options...
jguinch Posted March 17, 2016 Share Posted March 17, 2016 Try this : $SQL_Query_without_comments = StringRegExpReplace($SQL_Query,"(?m)^--\N*","") Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
maniootek Posted March 17, 2016 Author Share Posted March 17, 2016 does not work please note that "query.sql" looks like this: Link to comment Share on other sites More sharing options...
Dgameman1 Posted March 17, 2016 Share Posted March 17, 2016 Would you be able to copy and paste the query.sql file instead of taking a picture? Link to comment Share on other sites More sharing options...
jguinch Posted March 17, 2016 Share Posted March 17, 2016 (edited) 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 Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
maniootek Posted March 17, 2016 Author Share Posted March 17, 2016 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 Link to comment Share on other sites More sharing options...
jguinch Posted March 17, 2016 Share Posted March 17, 2016 And what happen if you have a string with "--" ? INSERT INTO `table` (`ID`, `NAME`, `TEXT`) VALUES (1, 'TheName', 'Some text -- and some text', ''); -- comment Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
Dgameman1 Posted March 17, 2016 Share Posted March 17, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
maniootek Posted March 17, 2016 Author Share Posted March 17, 2016 now it works, thank you one day i must learn more about regex, it is very powerfull option Link to comment Share on other sites More sharing options...
maniootek Posted April 12, 2016 Author Share Posted April 12, 2016 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) Link to comment Share on other sites More sharing options...
InunoTaishou Posted April 12, 2016 Share Posted April 12, 2016 $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. Link to comment Share on other sites More sharing options...
jchd Posted April 12, 2016 Share Posted April 12, 2016 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. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now