Parsing Insert SQL Query in Java using Regular Expression

Question

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.

Cà phê đen · Accepted Answer · 2016-11-30 07:19:11Z

7

You can try this regex:

((?<=(INSERT\\sINTO\\s))[\\w\\d_]+(?=\\s+))|((?<=\\()(\\s*[\\w\\d_,]+\\s*)+(?=\\)))

Explanation:

(?<=(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()) {
            System.out.println(m.group(0));
        }
     }
}

The output is like:

table_name
c1,c2,c3
abc,def,ghi
jkl,mno,pqr

edited Nov 30, 2016 at 7:19

answered Nov 30, 2016 at 7:03

Cà phê đen

1,9512 gold badges22 silver badges20 bronze badges

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

1 Comment

user990967 Over a year ago

Ty Never thought of using Look Behind functionality of Regex.

Mahesh Revaskar · Accepted Answer · 2020-10-08 11:09:55Z

1

Check if this works

(INSERT INTO) (\S+).*\((.*?)\).*(VALUES).*\((.*?)\)(.*\;?)

https://regex101.com/r/0wsoqJ/4

edited Oct 8, 2020 at 11:09

answered Oct 8, 2020 at 8:54

Mahesh Revaskar

1041 silver badge5 bronze badges

Comments

jj27 · Accepted Answer · 2016-11-30 05:02:53Z

0

For this specific example of your query, the following regex should do the trick.

INSERT INTO (\S+) \((\S+)\) VALUES \((\S+)\) , \((\S+)\)

https://regex101.com/r/2Kdayp/1

answered Nov 30, 2016 at 5:02

jj27

1785 bronze badges

1 Comment

user990967 Over a year ago

I want a solution for parsing a generic insert query.

Collectives™ on Stack Overflow

Parsing Insert SQL Query in Java using Regular Expression

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related