multi-line regex match

[Charles Knell]

I've got a file containing the DDL from a database. My goal is to pick it apart and create an XML file representing the structure that I can process further with XSLT.

A typical section I want to capture looks like this:

CREATE TABLE "SCHEMA"."TABLENAME"
   ("SYSTEM_ID" NUMBER(*,0),
"DOCNUMBER" NUMBER(*,0),
"VERSION_LABEL" VARCHAR2(10),
"APPLICATION" NUMBER(*,0),
"REF_LIBRARY" NUMBER(*,0),
"REF_DOCUMENT" NUMBER(*,0),
"ACTIVITY_TYPE" NUMBER(*,0),
"START_DATE" DATE,
"START_TIME" DATE,
"AUTHOR" NUMBER(*,0),
"TYPIST" NUMBER(*,0),
"ELAPSED_TIME" NUMBER(*,0),
"TYPE_TIME" NUMBER(*,0),
"KEYSTROKES" NUMBER(*,0),
"PAGES" NUMBER(*,0),
"BILLABLE" VARCHAR2(1),
"BILLED_ON" DATE,
"ACTIVITY_POSTED" VARCHAR2(1),
"CR_IN_USE" VARCHAR2(1),
"ACTIVITY_DESC" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 90 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 52428800 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTIN
CREASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DOCS"
 

So the important facts, as I see them, are:

I want to start the capture on a line that begins with "CREATE TABLE"

I want to stop the capture where there are three consecutive new line characters

My regex coding works for me when selecting patterns on a single line. For example, this procedure will correctly print the names of all the tables in the document:

      
public void extractTable() throws Exception {
  try{
      Pattern p = Pattern.compile("CREATE TABLE \"(.+)\"\\.\"(.+)\"", Pattern.CASE_INSENSITIVE;
      System.out.println("Pattern compiled.");
      Matcher m = p.matcher(this.getContent());
if(m.find()){
     System.out.println("Found table '" + m.group(2) + "'.");
             while(m.find()){
       System.out.println("Found table '" + m.group(2) + "'.");
     }
        }else{
             System.out.println("No Match");
        }
  }catch (Exception e){
      System.out.println("Error: " + e.getMessage());
       }finally{
 
       }
}

I have tried this pattern to capture the entire table definition, but the program doesn't print anything after several minutes of running.

CREATE TABLE \"(.+)\"\\.\"(.+)\"\\n\\n", Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE

I may have an issue with greedy matching when I want non-greedy matching, but I am rusty on REGEX and anyway, my biggest experience with it was in perl, where things are very different.

Please, can someone correct my pattern? Thanks

[Alan Moore]

Try this:

Pattern p = Pattern.compile("CREATE +TABLE +\"([^\"]+)\"\\.\"([^\"]+)\"(.+?)\n\n\n",
    Pattern.CASE_INSENSITIVE | Pattern.DOTALL);

[Charles Knell]

Thanks for your reply, but it did not produce a match.

[Dave Wingate]

Could you pre-process your content by:
1) replacing matches on 3 carriage returns with a symbol that's not commonly used in SQL, say, "^".
2) remove all remaining carriage returns.

I've never tried this, but thought it might be a simple (though inellegant) way to solve the problem.

[Charles Knell]

I'm willing to go ugly early, but I still need to find a pattern that matches three consecutive new lines.

[Charles Knell]

O.K. I'm making progress. This pattern matches exactly three new lines. Because I'm working on Windows, I need both the "\r" and "\n".

 (\\r\\n){3}