user990967 user990967 - 9 months ago 68
Java Question

Parsing Insert SQL Query in Java using Regular Expression

I want to parse the Insert Query in Java using Regex.

Following is the sample string

INSERT INTO table_name (c1,c2,c3) VALUES (abc,def,ghi) , (jkl,mno,pqr)

I want the following output:

Group1: table_name
Group2: c1,c2,c3
Group3: abc,def,ghi
Group4: jkl,mno,pqr

I have tried the following regular Expression:

INSERT INTO ([A-Za-z][A-Za-z0-9_-]*) (?:\((.*)\))?\s*VALUES (\((,)?(.*)\))*

The output is

Group1 : table_name
Group2 : c1,c2,c3
Group3 : (abc,def,ghi) , (jkl,mno,pqr)
Group4 : Empty
Group5 : abc,def,ghi) , (jkl,mno,pqr

Please help me how to get the desired result.

Answer Source

You can try this regex:



(?<=(INSERT\\sINTO\\s))[\\w\\d_]+(?=\\s+) matches [\\w\\d_]+ between (INSERT\\sINTO\\s) and \\s+

(?<=\\()(\\s*[\\w\\d_,]+\\s*)+(?=\\)) matches [\\w\\d_,]+ between \\( and \\)

Here is an example code:

import java.util.regex.*;

public class HelloWorld {
    public static void main(String []args){
        String test = "INSERT INTO table_name (c1,c2,c3) VALUES (abc,def,ghi) , (jkl,mno,pqr)";

        String regex = "((?<=(INSERT\\sINTO\\s))[\\w\\d_]+(?=\\s+))|((?<=\\()([\\w\\d_,]+)+(?=\\)))";

        Pattern re = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);

        Matcher m = re.matcher(test);
        while (m.find()) {

The output is like: