CODEWITHSUNDEEP
androidsqlregex
Drawn
Drawn

Reputation: 445

RegEx to extract the table name from a SQL script file

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;
}

Upvotes: 3

Views: 2762

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626903

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);).

Upvotes: 2

Related Questions