Splitting on comma either outside quotes when escaped quotes exist, or outside brackets

Question

Is it possible to split a String with the conditions as below?

• split by , (i.e. comma)
• on each element, ignore checking comma within the first ' and the last '
• on each element, ignore checking comma within the first ( and the last )

e.g.

String source = "to_char(DATE, 'YYYY,MM,DD'), 'I am sad :(', to_char(DATE, ('YYYY(MM,DD)')), to_char(DATE, ('YYYY,MM,DD)')), to_char(DATE, ('YYYY(MM,DD')), NAME, to_char(DATE, '(YYYY)MM,DD'), CITY || ', (UK)', CITY || ', US''s CITY', CITY || ', UK'";

String[] expected = new String[]{
"to_char(DATE, 'YYYY,MM,DD')", 
"'I am sad :('",
"to_char(DATE, ('YYYY(MM,DD)'))", // brackets within quotes within brackets
"to_char(DATE, ('YYYY,MM,DD)'))", // missing open bracket in quotes
"to_char(DATE, ('YYYY(MM,DD'))", // missing close bracket in quotes
"NAME", 
"to_char(DATE, '(YYYY)MM,DD')", 
"CITY || ', (UK)'", 
"CITY || ', US''s CITY'", // escape a single quote in quotes
"CITY || ', UK'"
};

String[] result = splitElements(source);
assert expected.equals(result);

The first 2 bullet points can be achieved by Splitting on comma outside quotes when escaped quotes exist

This would be really useful when manipulating with SQL. E.g. split the items, append, insert, prepend items etc.

Thanks in advance.

Answer 1

I know it is kind of long but fairly straight-forward, just keep track of how many parens and inside or outside quotes.

String[] splitElements(String source) {
    int parencount = 0;
    boolean q = false;
    List<String> l = new ArrayList<>();
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < source.length(); i++) {
        char c = source.charAt(i);
        switch (c) {
            case ',':
                if (!q && parencount == 0) {
                    l.add(sb.toString());
                    sb.setLength(0);
                } else {
                    sb.append(c);
                }
                break;

            case '(':
                if(!q) parencount++;
                sb.append(c);
                break;

            case ')':
                if(!q) parencount--;
                sb.append(c);
                break;

            case '\'':
                q = ! q;
                sb.append(c);
                break;

            default:
                sb.append(c);
                break;
        }
    }
    String last = sb.toString();
    l.add(last);
    String sa[] = l.toArray(new String[l.size()]);
    return sa;
}

Answer 2

You can done this by using split method of java class String together with this

Regex:

(?<!\([^\(\)']{0,100}),(?![^\(\)']*\))(?=(?:'[^']*'|[^'])*$)

Explanation:

(?<!\([^\(\)']{0,100}),(?![^\(\)']*\))

Any , which are not surrounded by (...), note that generally negative lookbehind is required a finite quantifier here I arbitrary use {0,100}.

(?=(?:'[^']*'|[^'])*$)

Use lookahead to guarantee that either there is even number of ' which count from , till the end of the string (regex: (?:'[^']*')*$) or there is string which composed of any characters except ' till the end of the string (regex: [^']*$).

Strategy:

1. Using negative lookahead, negative lookbehind to guarantee that , is out of (...),
2. Using lookahead to guarantee that there are only pair of 's ahead which count from , till the end of the string.

Code Example:

public class Main{

    public static void main(String[] args) {

        String source = "to_char(DATE, 'YYYY,MM,DD'), to_char(DATE, ('YYYY(MM,DD)')), " +
                        "to_char(DATE, ('YYYY,MM,DD)')), to_char(DATE, ('YYYY(MM,DD')), " +
                        "NAME, to_char(DATE, '(YYYY)MM,DD'), CITY || ', (UK)', " +
                        "CITY || ', US''s CITY', CITY || ', UK', " +
                        "'I am sad :(', to_char(DATE, 'YYYY,MM,DD')";

        String delimiters = "(?<!\\([^\\(\\)']{0,100}),(?![^\\(\\)']*\\))(?=(?:'[^']*'|[^'])*$)";

        String[] tokens = source.split(delimiters);

        for(String token : tokens) {
            System.out.println(token.trim());
        }
    }
}

Output:

to_char(DATE, 'YYYY,MM,DD')
to_char(DATE, ('YYYY(MM,DD)'))
to_char(DATE, ('YYYY,MM,DD)'))
to_char(DATE, ('YYYY(MM,DD'))
NAME
to_char(DATE, '(YYYY)MM,DD')
CITY || ', (UK)'
CITY || ', US''s CITY'
CITY || ', UK'
'I am sad :('
to_char(DATE, 'YYYY,MM,DD')