Split by comma pattern matching

Question

What pattern can I use to split a string like this:

f.id AS id, CONCAT(a1.id, a2.id, a3.id) AS cnp, SUM(A3.nr) AS sum

in such a way that the result is an array of 3 groups like this:

f.id AS id
CONCAT(a1.id, a2.id, a3.id) AS cnp
SUM(A3.nr) AS sum

Can I match a comma that is not enclosed by parentheses?

@Jean-FrançoisSavard - That also splits with all the commas between the parens. — DaoWen, Feb 25 '16 at 12:48
i´d rather use a [Parser](http://stackoverflow.com/questions/660609/sql-parser-library-for-java) for the sql syntax instead of writing your own for this job. — SomeJavaGuy, Feb 25 '16 at 12:52

MT0 · Answer 1 · 2016-02-25T13:05:45.893

The pattern appears to always take the format ... AS ... and you can just use a regular expression to match that:

Pattern p = Pattern.compile("(.*? as .*?)(,|$)", Pattern.CASE_INSENSITIVE );
String query = "f.id AS id, CONCAT(a1.id, a2.id, a3.id) AS cnp, SUM(A3.nr) AS sum";
Matcher m = p.matcher( query );
while ( m.find() ){
    System.out.println( m.group(1) );
}

IDEONE

So long as you are not expecting any correlated sub-queries to be nested in your select values (or other edge cases such as strings containing ' as error,' AS id, ...) then this ought to work for inputs similar to your format.

score 0 · Answer 2 · answered Feb 25 '16 at 12:48

Probably there is a killer regular expression for this, but what would be more maintanable could be to:

Temporarily set placeholders on blocks between parentheses
Split the result on the desired separator
Replace the placeholders with their original values

To make step 1 more general, you should insert placeholders at sections where the separator should not function. As long as you are able to accurately determine what those sections are, you could apply this recipe.

Andy Turner · Answer 3 · 2016-02-25T12:57:12.753

Using an actual SQL Parser, as suggested by @KevinEsche, is probably the most robust choice.

However, if you don't require parsing of all SQL expressions, I would just use plain old char matching: go through the string a character at a time, counting how deeply nested in the brackets you are:

List<String> parts = new ArrayList<>();
int i = 0;
int depth = 0;
while (i < str.length()) {
  int start = i;
  while (i < str.length()) {
    char ch = str.charAt(i);
    if (ch == '(') {
      depth++;
    } else if (ch == ')') {
      depth--;
    } else if (ch == ',' && depth == 0) {
      break;
    }
    i++;
  }
  // Maybe check that depth == 0 here.
  parts.add(str.substring(start, i));
  i++;  // To skip the comma.
}

score 0 · Answer 4 · answered Feb 25 '16 at 14:56

Thank you for your answers. I tried to vote but I can't yet. I used look ahead pattern to solve the problem:

String pattern = ",(?!([^(]*\\)))";
String str = "f.id AS id, CONCAT(a1.id, a2.id, a3.id) AS cnp, SUM(A3.nr) AS sum";
String strg [] = str.split(pattern);
for(int i=0;i<strg.length;i++) {
    System.err.println("Group "+i+" is "+strg[i]);
}

And the result is:

Group 0 is f.id AS id

Group 1 is CONCAT(a1.id, a2.id, a3.id) AS cnp

Group 2 is SUM(A3.nr) AS sum

score 0 · Answer 5 · answered Mar 04 '16 at 20:18

In the end is too complicated to write a SQL Parser so I decided to use ANTLR4.

I used the example from here and works fine. https://github.com/bkiers/sqlite-parser

But I don't know how to extract only some parts of the query(select, joins, order...) and I can't find any examples online. Can someoane show how this is done?

Thank you.

Split by comma pattern matching

5 Answers5