regex - Read and extract the multiple SQL queries in a String and store them in a list in Java

Question

I need to extract only the SQL queries(all of them) and store them in a list to pass an argument in Java.

This is the sample input string

section Section1; shared Data_first = let Source = Sourcename.Database("abc.net", [HierarchicalNavigation=true , Query=" SELECT Company_ , Corporate_ , Corporate_Name , Group_Name , Division_Name , Market_Name FROM Entity_table WHERE CODE = '12345' "]), in #"Renamed Columns"; shared Data_first = let Source = Sourcename.Database("abc.net", [HierarchicalNavigation=true , Query=" SELECT column1, column2 FROM table1, table2 WHERE column2='value'; "]), in #"Renamed Columns"; shared Data_first = let Source = Sourcename.Database("abc.net", [HierarchicalNavigation=true , Query="SELECT Company_ , Corporate_ , Corporate_Name , Group_Name , Division_Name , Market_Name FROM Entity_table_three WHERE CODE = '78901'"]), in #"Renamed Columns";

Here is the code that I tried, but it only fetches the first query. I need all the queries present in the String. I am not familiar with regex so have not tried it.

public static String ReadBigStringIn(BufferedReader buffIn, String st) throws IOException {
    StringBuilder everything = new StringBuilder();
       
    StringBuilder lines = null ;
    while( (st = buffIn.readLine()) != null) {
        lines = everything.append(st);
    }
    String myQuery = lines.toString().substring(lines.toString().indexOf("Query"));
    System.out.println("myQuery: 
"+ myQuery);
    String query= substringBetween(myQuery, "Query="", ""])");
    System.out.println("myQuery2: 
"+ query);
    return query;

Thanks in advance!

question from:https://stackoverflow.com/questions/65640830/read-and-extract-the-multiple-sql-queries-in-a-string-and-store-them-in-a-list-i

Answer 1

You can use positive lookahead and positive lookbehind to capture the substring between the texts Query=" and the next " with this regex: (?<=Query=\")\s*(.*?)(?=\") :

Pattern pattern = Pattern.compile("((?<=Query=\").*?(?=\"))");
Matcher matcher = pattern.matcher(lines);
while(matcher.find()) {
    System.out.println(matcher.group(1));
}

• (?<=Query=") : a positive lookbehind, matches if a string is preceded by Query="
• \s* : zero-or-more whitespace, to get rid of spaces after the "
• (.*?) : match anything, ? non-greedily (till the next ")
• (?="): a positive lookahead, matches if a string is followed by "

Output:

SELECT Company_ , Corporate_ , Corporate_Name , Group_Name , Division_Name , Market_Name FROM Entity_table WHERE CODE = '12345' 
SELECT column1, column2 FROM table1, table2 WHERE column2='value'; 
SELECT Company_ , Corporate_ , Corporate_Name , Group_Name , Division_Name , Market_Name FROM Entity_table_three WHERE CODE = '78901'