RegEx to extract the table name from a SQL script file

Question

I have a routine that attempts to read an SQL script file and extract the table name from the residing SQL statement(s). Below, for example, is possibly a typical SQL script file:

-- Drop the table first
DROP TABLE IF EXISTS working;

-- SQL Statement to create a new database.
CREATE TABLE IF NOT EXISTS working(
ToDoItem VARCHAR
, ToDoDateTime VARCHAR
, ToDoDateTimeEpoch Long
, ToDoTimeZone VARCHAR
, ToDoReminderEpoch Long
, ToDoReminderChk integer default 0
, ToDoLEDColor integer default 0
, ToDoFired integer default 0
, deleted integer default 0
);

Regular expression I use is not valid. I'm getting the error, Look-behind pattern matches must have a bounded maximum length near index 58.

I'm still a noob to regex, and so I got that current expression from StackOverflow only to encounter now the max length issue:

(?<=\\b(exists|from|join)\\s+[a-zA-Z0-9_$#-]*\\.{0,1}\\s{1,1})[a-zA-Z0-9_]+

Code:

    private static String tableName() {

    String name = "";

    Pattern pattern = Pattern.compile("(?<=\\b(exists|from|join)\\s+[a-zA-Z0-9_$#-]*\\.{0,1}\\s{1,1})[a-zA-Z0-9_]+");

    Matcher matcher;

    try {

        for (String sqlStmt : dbSQLParser.parseSqlFile(SQL_DIR + "/" + CREATE_FILE, appController.MainActivity().getAssets())){

            matcher = pattern.matcher(sqlStmt);

            if (matcher.find()){

                name = matcher.group(1);

                break;
            }
        }

    } catch (IOException ex) {

        ErrorHandler.logError(ex);
    }

    return name;
}

Wiktor Stribiżew · Accepted Answer · 2015-05-13 19:13:31Z

2

If you are planning to extract working, your regex does not match even if you remove the look-behind. You need to match ; or ( at the end. Perhaps, you want to allow for dot + alphanumerics. Then, you can use this regex:

\b(?:exists|from|join)\s+([a-zA-Z0-9_$#-]*\.?\s*(?:[a-zA-Z0-9_]+)*)

See demo

Pattern pattern = Pattern.compile("(?i)\\b(?:exists|from|join)\\s+([a-zA-Z0-9_$#-]*\\.?\\s*(?:[a-zA-Z0-9_]+)*)");

And the name will be in Group 1 (matcher.group(1);).

answered May 13, 2015 at 19:13

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

2 Comments

Drawn Over a year ago

Excellent! It's looking good. Thank-you. I particularly glad you presented your solution in the demo. Now that's a tool I will certainly have to take advantage of. Thanks again.

Wiktor Stribiżew Over a year ago

Oh, I should warn you a bit about the demo site. Regex101 does not have support for Java-specific regexps. For this case, it does not matter, the pattern will work the same (though you see I have to add and remove slashes). For Java-specific regex, you'd better try regexplanet.com

Collectives™ on Stack Overflow

RegEx to extract the table name from a SQL script file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related