I need to write a script to search through a SQL file and add "NOT NULL" to all the table creation elements that don't otherwise specify. I'm a little bogged down on the mechanics, though. Here's a concrete example:

create table foo (
   somevar1 sometype,
   somevar2 sometype null,
   somevar3 sometype not null,
   somevar4 sometype(20),
   somevar5 sometype(20) null,
   somevar6 sometype(10))
[download]

In this case, I'd want to add the string "NOT NULL" to the end of variables 1, 4, and 6, and leave the others alone. I've entered the example in a nicely indented fashion, but in practice, I have no control over the formatting. There might be any amount or type of whitespace wherever SQL allows it. Also, there might be any number of "create table" blocks in a given file.

I've got a pattern that gets me most of the way there, but it's only hitting the last element in each create table list. How do I do this correctly?

$str =~ s{
   (create\s+table\s+\w+\s*\()   # "create table foo (", save as $1
   (                             # Grab this pattern (each of the crea
+te table variables), save as $2
      (                          # Save as $3
         \s*                     # Any spaces
         \w+                     # Variable name
         \s+                     # At least one space
         \w+                     # Variable type
         (                       # Optional (nnn) for character string
+ types, save as $4
            \(                   # Literal open paren
            \d+                  # Any number of digits
            \)                   # Literal close paren
         )?                      # The ? makes it optional
         \s*                     # Any spaces
      )                          # "varname vartype " (not the null/no
+t null part)
      (                          # Save as $5
         [\w\s]*                 # Any words and/or spaces ("null" or 
+"not null", specifically)
      ) 
      (                          # Save as $6
         ,?                      # Optional comma (optional as it may 
+be the last element)
         \s*                     # Any spaces
      )
   )+                            # Match this whole section at least o
+nce
   (\s*\)\s*)                    # Closing paren for create table bloc
+k with surrounding whitespace, save as $7
}
{
      print $1;
      print $3;
      if ($5 =~ /null/i) {
         print $5;
      } else {
         print " NOT NULL";
      }
      print $6;
      print $7;
}gex;

# Output: create table foo (somevar6 sometype(10) NOT NULL)
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.