Regex for reading 'create table' sql statements

Question

I am currently writing this manually using a line read from a file and am trying to read all table ddls where the table begins a_

An input of this:

Other stuff: 

Other stuff: 
create table a_table1 (
    id number(10,0) not null,
    timestamp number(19,0) not null,
    primary key (id)
)
stuff
create table a_table2 (
    id number(10,0) not null,
    primary key (id)
)

Other stuff: 
create table b_table1 (
    id number(10,0) not null,
    timestamp number(19,0) not null,
    primary key (id)
)
other stuff 

other stuff

should output only this

create table a_table1 (
    id number(10,0) not null,
    timestamp number(19,0) not null,
    primary key (id)
)
create table a_table2 (
    id number(10,0) not null,
    primary key (id)
)

Currently I am using LineReaders and remembering when I see create table and then reading everything until I see )

Is this the most efficient way? Is there some fancy reg ex I could use?

I tried the following reg ex but this didnt work as it just returns the whole string again. Perhaps the new lines are breaking it

"^.*create.*a_(.*?)\\).*$", "$1")

Any advice would be appreciated

Thanks

Answer 1

Try something like this:

    ByteArrayOutputStream baos = new ByteArrayOutputStream();

    IOUtils.copyLarge(getClass().getClassLoader().getResourceAsStream("input.txt"), baos);
    String org = baos.toString();

    final Pattern compile = Pattern.compile("(?s)(create table a_.*?\n\\)\n)");
    final Matcher matcher = compile.matcher(org);
    while (matcher.find()) {
        System.out.println(matcher.group());
    }

input.txt

Other stuff:

Other stuff:
create table a_table1 (
    id number(10,0) not null,
    timestamp number(19,0) not null,
    primary key (id)
)
stuff
create table a_table2 (
    id number(10,0) not null,
    primary key (id)
)

Other stuff:
create table b_table1 (
    id number(10,0) not null,
    timestamp number(19,0) not null,
    primary key (id)
)
other stuff

output

create table a_table1 (
    id number(10,0) not null,
    timestamp number(19,0) not null,
    primary key (id)
)
create table a_table2 (
    id number(10,0) not null,
    primary key (id)
)