CODEWITHSUNDEEP
regexstringnotepad++comments
gba
gba

Reputation: 57

How to comment SQL statements in Notepad++?

How can I "block comment" SQL statements in Notepad++?

For example:

CREATE TABLE gmr_virtuemart_calc_categories (
  id int(1) UNSIGNED NOT NULL,
  virtuemart_calc_id int(1) UNSIGNED NOT NULL DEFAULT '0',
  virtuemart_category_id int(1) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

It should be wrapped with /* at the start and */ at the end using regex in Notepad++ to produce:

/*CREATE TABLE ... (...) ENGINE=MyISAM DEFAULT CHARSET=utf8;*/

Upvotes: 0

Views: 2045

Answers (2)

mickmackusa
mickmackusa

Reputation: 47894

You only offer one sample input, so I am forced to build the pattern literally. If this pattern isn't suitable because there are alternative queries and/or other interfering text, then please update your question. Tick the "Match case" box.

Find what: (CREATE[^;]+;) Replace with: /*$1*/

Otherwise, you can use this for sql query blocks that start with a capital and end in semicolon:

Find what: ([A-Z][^;]+;) Replace with: /*$1*/

To improve accuracy, you might include ^ start of line anchors or add \r\n after the semi-colon or match the CHARSET portion before the semi-colon. There are several adjustments that can be made. I cannot be confident of accuracy without knowing more about the larger body of text.

Upvotes: 1

user557597
user557597

Reputation:

You could use a recursive regex.
I think NP uses boost or PCRE.
This works with both.

https://regex101.com/r/P75bXC/1

Find (?s)(CREATE\s+TABLE[^(]*(\((?:[^()']++|'.*?'|(?2))*\))(?:[^;']|'.*?')*;)
Replace /*$1*/

Explained 

 (?s)                           # Dot-all modifier
 (                              # (1 start) The whole match
      CREATE \s+ TABLE [^(]*    # Create statement
      (                              # (2 start), Recursion code group
           \(
           (?:                            # Cluster group
                [^()']++                       # Possesive, not parenth's or quotes
             |                               # or,
                ' .*? '                        # Quotes (can wrap in atomic group if need be)
             |                               # or,
                (?2)                           # Recurse to group 2
           )*                             # End cluster, do 0 to many times
           \)
      )                              # (2 end)
                                     # Trailer before colon statement end
      (?:                            # Cluster group, can be atomic (?> ) if need be
           [^;']                          # Not quote or colon 
        |                               # or,
           ' .*? '                        # Quotes
      )*                             # End cluster, do 0 to many times   
      ;                              # Colon at the end
 )                              # (1 end)

Upvotes: 0

Related Questions