Regex to extract column name and values not working

Question

In java I am trying to extract column names and their values using Regex and Matcher but dont know what I am doing wrong here.

    String sql = "INSERT INTO MyTable (column1, column2, column3, column4, column5 ) VALUES (1, 'Hi', 'A,B', '', null)";
    String pattern = "INSERT INTO.*((\\w)+).*\\((.*)\\).*VALUES.*\\((.*)\\)";

    Pattern r = Pattern.compile(pattern);

    Matcher m = r.matcher(sql);
    if (m.find()) {
        System.out.println("Found value: " + m.group(0));
        System.out.println("Found value: " + m.group(1));
        System.out.println("Found value: " + m.group(2));
    } else {
        System.out.println("NO MATCH");
    }

Expectation:

Group-1 = column1, column2, column3, column4, column5
Group-2 = 1, 'Hi', 'A,B', '', null

anubhava · Accepted Answer · 2016-11-18 10:54:55Z

2

Don't use greedy .* in your regex. You can use this regex:

\bINSERT\s+INTO\s+\S+\s*\(([^)]+)\)\s*VALUES\s*\(([^)]+)\)

In Java:

String regex = "\\bINSERT\\s+INTO\\s+\\S+\\s*\\(([^)]+)\\)\\s*VALUES\\s*\\(([^)]+)\\)";

This will give:

Group 1: "column1, column2, column3, column4, column5 "
Group 2: "1, 'Hi', 'A,B', '', null"

RegEx Demo

answered Nov 18, 2016 at 10:54

anubhava

790k67 gold badges603 silver badges671 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrew Magerman Over a year ago

that's an even better answer than mine. I love the replacement of the greedy operator with 'match everything except back bracket

amicngh Over a year ago

@anubhava Thanks! It worked. can you help me to extract the values separated by ','. I am having trouble when splitting values with ',' (comma) because one value itself has comma inside.

anubhava Over a year ago

Hmm splitting value on comma but ignoring commas inside the quotes is not trivial problem. You see this and this

Andrew Magerman · Accepted Answer · 2016-11-18 11:00:38Z

1

Try this

"INSERT INTO.*\\((.*)\\).*VALUES.*\\((.*)\\)"

The mistake you were doing is not escaping the brackets. Without the escaping \( Regex assumes you are starting a group.

answered Nov 18, 2016 at 11:00

Andrew Magerman

1,4231 gold badge16 silver badges26 bronze badges

Collectives™ on Stack Overflow

Regex to extract column name and values not working

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related