CODEWITHSUNDEEP
sqlregexgeneral-log
Risa
Risa

Reputation: 29

Regex Filter Newline Search

I need to filter the query from a log and it needs look up the newlines and i have stuck in regex filtering the new lines

here is the example of the log that i want to filter

      139 Connect   root@localhost as anonymous on 
      139 Query SET CHARACTER SET 'utf8mb4'
      139 Query SET collation_connection = 'utf8mb4_unicode_ci'
      139 Query SELECT CURRENT_USER()
      139 Query FLUSH PRIVILEGES
      139 Query SELECT * FROM `mysql`.`db` LIMIT 1
      139 Query DELETE FROM `mysql`.`db` WHERE `host` = "" AND `Db` = "" AND `User` = "" LIMIT 1
      139 Query INSERT INTO `mysql`.`db`(`host`, `Db`, `User`) VALUES("pma_test_host", "mysql", "pma_test_user")
      139 Query DELETE FROM `mysql`.`db` WHERE host = "pma_test_host" AND Db = "mysql" AND User = "pma_test_user" LIMIT 1
      139 Query UPDATE `mysql`.`db` SET `host` = "" WHERE `host` = "" AND `Db` = "" AND `User` = "" LIMIT 1

from that log text i have used regex like this

(?=Query).*

but the search result only in a line 

Query   SET CHARACTER SET 'utf8mb4'

because i use the character of "." in regex which mean not doing search in a new line. i've tried to put "\n" in few way but it doesn't work. i need a suggestion to get result like this:

Query   SET CHARACTER SET 'utf8mb4'
          139 Query SET collation_connection = 'utf8mb4_unicode_ci'
          139 Query SELECT CURRENT_USER()
          139 Query FLUSH PRIVILEGES
          139 Query SELECT * FROM `mysql`.`db` LIMIT 1
          139 Query DELETE FROM `mysql`.`db` WHERE `host` = "" AND `Db` = "" AND `User` = "" LIMIT 1
          139 Query INSERT INTO `mysql`.`db`(`host`, `Db`, `User`) VALUES("pma_test_host", "mysql", "pma_test_user")
          139 Query DELETE FROM `mysql`.`db` WHERE host = "pma_test_host" AND Db = "mysql" AND User = "pma_test_user" LIMIT 1
          139 Query UPDATE `mysql`.`db` SET `host` = "" WHERE `host` = "" AND `Db` = "" AND `User` = "" LIMIT 1

Upvotes: 1

Views: 399

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626926

You may use a regex like

(?=Query)[\s\S]*

It will check if there is Query immediately to the left of the current location and then will consume (will put to the match value) any 0+ chars (including line breaks).

Upvotes: 0

Related Questions