Parsing SQL table definition using regular expression in Java

Question

Im trying to parse a SQL table creation script in Java.

Ive currently got the following pattern:

Pattern p = Pattern.compile("(.+)([ ]+)(.+)([ ]+)(.+)");

i.e a group of any chars (column name), followed by one or more spaces, followed by another group of chars (column type), followed by one or more spaces, followed by any number of chars (i.,e not null etc).

And this is used by the following code:

Matcher m = p.matcher(field);
if(m.find()){
    String column = m.group(1).trim();
    String type = m.group(3).trim();
    String clauses = m.group(5).trim();
}

And yet when I run this on:

firstColumn         varchar(4)   not null,

The first group is:

firstColumn         varchar(4)

I would expect the three extracted fields to be firstColumn, varchar(4) and not null respectively.

Any ideas?

I just put this here in case you do not insist on regex https://stackoverflow.com/questions/5572793/sql-parser-library-for-java-retrieve-the-list-of-table-names-present-in-a-sql-s https://stackoverflow.com/questions/660609/sql-parser-library-for-java — bpgergo, Sep 05 '11 at 12:41
Wish I could +2: no regex, pre-built solution (Especially the proper parser +298345). — Jonathan Dickinson, Sep 05 '11 at 16:22

score 2 · Accepted Answer · answered Sep 05 '11 at 12:33

(.+) will consume as much as possible. To make it consume as little as possible, change it to (.+?).

Try something like this:

String input = "firstColumn         varchar(4)   not null,";

Pattern p = Pattern.compile("(.+?)\\s+(.+?)\\s+(.*)");
Matcher m = p.matcher(input);

if (m.find()) {
    System.out.println(m.group(1));
    System.out.println(m.group(2));
    System.out.println(m.group(3));
}

Output:

firstColumn
varchar(4)
not null,

score 0 · Answer 2 · answered Sep 05 '11 at 12:42

Another option (IMHO a preferable one) to lazy matching is specifying the characters that can happen in the word, that is:

([^ ]+)([ ]+)([^ ]+)([ ]+)(.+)([ ]+)

The difference is that while this approach will never put spaces in columnName, the lazy match still might if the rest of the pattern fails and backtracks.

BTW, the data type can still contain spaces (eg. CHAR(20) CHARACTER SET xxxxx, also the parens may be spaced), so this approach is not really going to work.

Parsing SQL table definition using regular expression in Java

2 Answers2