2

I am looking for a regular expression to remove comment lines from a sql fine. All of the comments start with "COMMENT ON" (obviously) but may tend to have more than one line. I was able to come up with an expression to remove a single line but am struggling with multiple lines. A typical entry I am looking to remove looks like this:

COMMENT ON TABLE account_heading IS $$
This table holds the account headings in the system.  Each account must belong 
to a heading, and a heading can belong to another heading.  In this way it is 
possible to nest accounts for reporting purposes.$$;

So what I need is a regular expression that follows multiple lines up to the point it sees a semicolon.

I came up with this one that will search to the second line and stop at the double dollar ($$). I just started with RegEx yesterday so forgive me if this is absolutely completely wrong (which I am sure it is):

^COMMENT ([^\n\r]+)[\n\r]([\$;\n\r]+)

I am doing this in TextWrangler with the Grep option on a Mac.

Thanks!

agregory23
  • 23
  • 3
  • If you need help, people will help. If you need code, write code. Maybe post your one-line regex – keyser Aug 08 '12 at 19:48
  • Added it. Thanks for the tip. Not trying to have people do it for me, its just that I have been at this for a while and regex seems like it takes some time to learn. Just trying to know when I might be in over my head for a given task. – agregory23 Aug 08 '12 at 19:51
  • And SO is perfect for that. Just trying to help you avoid the haters :p – keyser Aug 08 '12 at 20:54

2 Answers2

0

Maybe something like COMMENT\sON.*?\$\$; - it should work if you are able to set your editor to match on multiple lines (usually by specifying the s flag).

jmosbech
  • 1,128
  • 9
  • 8
0

Well, I don't know SQL very well, but if you want the content between the '$$', just use:

\${2}[^\${2}]*\${2};

If you want everything after the 'IS' Word:

COMMENT .*? IS ([^;]*)

Matches anything that isn't a semicolon (get the first group, not the match).

Evandro Silva
  • 1,392
  • 1
  • 14
  • 29
  • That did it for many of them! Thanks! It actually got up to the $$ and not the semicolon. So I threw a semicolon at the end and it grabbed the whole thing. – agregory23 Aug 08 '12 at 21:10
  • Any idea why it would ignore some of the entries? Could it be the type of line break? Unfortunately commenting here removes the formatting of the lines if I try to add them. – agregory23 Aug 08 '12 at 21:12
  • Ah, it was a spacing issue. I removed the space after IS and made it "COMMENT .*? IS([^;]*);" and it got the rest! Thank you so much! – agregory23 Aug 08 '12 at 21:18