Split multiple SQL statements into individual SQL statements

I'm not sure this is possible at all. You would certainly need an in-depth knowledge of the SQL syntax of your target DBMS. For example just off the top of my head this is a single MySQL statement:

INSERT INTO things
SELECT * FROM otherthings ON DUPLICATE KEY
UPDATE thingness=thingness+1

It is likely there are constructs in some DBMSs that, without a delimiter, could be ambiguous.

I don't want to require the user to enter a semi-colon after each SQL statement.

I think you may be forced to. It's totally the standard way to delimit SQL statements. Even if you can find a heuristic to detect probably-start-of-SQL-statement points, you risk disasters like an accidental “DELETE FROM things”-without-WHERE-clause.

SQL statements can be on one or multiple lines, so I can't wrap on LBs/CRs

Would double-newline-for-new-statement be acceptable?

I tried some RegEx attempts, but that doesn't seem to be powerful enough.

No, even with semicolon delimiters, regex is nowhere near powerful enough to parse SQL. Problem points would include:

';'
";"
`;`
'\';'
''';'
-- ;
#;
/*;*/

and any interposition of these structures. Eek!

Maybe try this library. I have used it successfully for parsing sql in the past. http://www.sqlparser.com/

To add a quirk to the discussion that periodically causes issues:

DECLARE c CURSOR FOR
    SELECT * FROM SomeWhere ...
        FOR UPDATE

The trailing UPDATE tends to throw ad hoc parsers off their stride. It may well be that you don't have to worry about that because the DECLARE notation (which is really Embedded SQL, not plain SQL) is not permitted in the first place. But the FOR UPDATE clause can appear in some dialects of SQL even when not in a DECLARE statement, so beware.

maybe with the following Java Regexp? check the test...

@Test
public void testRegexp() {
    String s = //
        "SELECT 'hello;world' \n" + //
        "FROM DUAL; \n" + //
        "\n" + //
        "SELECT 'hello;world' \n" + //
        "FROM DUAL; \n" + //
        "\n";

    String regexp = "([^;]*?('.*?')?)*?;\\s*";

    assertEquals("<statement><statement>", s.replaceAll(regexp, "<statement>"));
}

    $sMultiQuery = 'SHOW TABLES; SELECT * FROM `test`';
    $aQueries    = array();

    if ( preg_match_all('/([^;]*?((\'.*?\')|(".*?"))?)*?(;\s*|\s*$)/', $sMultiQuery, $aMatches) )
    {
        $aQueries = $aMatches[0];
    }
    else
    {
        $aQueries = array($sMultiQuery);
    }

    foreach ( $aQueries as $sQuery )
    {
        # Do your thing
    }

If anybody needs a javascript / typescript function to split SQL statements here you go

const splitSqlStatements=(statements:string):string[]=>{
    const split:string[]=[];
    let inside:'"'|"'"|'-'|null=null;
    let s=0;
    for(let i=0;i<statements.length;i++){
        const char=statements[i] as string;

        if(inside){
            if(inside==='-'){
                if(char==='\n'){
                    inside=null;
                    s=i+1;
                }
            }else if(char===inside){
                if(statements[i+1]===inside){
                    i++;
                }else{
                    inside=null;
                }
            }
        }else if(char==='"'){
            inside='"';
        }else if(char==="'"){
            inside="'";
        }else if(char==='-' && statements[i+1]==='-'){
            const v=statements.substring(s,i).trim();
            if(v){
                split.push(v);
            }
            inside='-';
            i++
        }else if(char===';'){
            const v=statements.substring(s,i).trim();
            if(v){
                split.push(v);
            }
            s=i+1;
        }
    }
    if(s!==statements.length-1 && inside!=='-'){
        const v=statements.substring(s).trim();
        if(v){
            split.push(v);
        }
    }
    return split;
}

Your best bet is to require the user to put some type of deliminator between statements. For example: require each statement be delineated with a line containing only the word GO, or a "\", or end each statement with a ";".

This way you can easily break the single string into separate SQL statements.

If you don't want your users to put in a delimiting character such as ';' or any thing else, you will need to parse the input yourself and have logic to determine where statements begin.

Your logic will need to deal with the obvious query starting keywords 'SELECT', 'UPDATE', 'INSERT', 'DELETE' and work forward to the next keyword (or end of input).

Have you tried using the keywords 'SELECT', 'UPDATE', 'INSERT' and 'DELETE' combined with counting the number of opening '(' and closing braces ')' ?

This should allow you to determine avoid nested SELECT statements and find the correct end of the statement.

You need to require the semicolon delimiter. Technically, without it a SQL statement is completely invalid; anyone omitting it is writing malformed SQL. Requiring the semicolon solves all of your problems, in a standardized way, and makes the software easy to write.

Perhaps do the following: if the user enters a query not containing one or more semicolons (outside of quotes, of course), add a semicolon at the end and run it as a single query. Otherwise, split the entered queries at semicolons and run each one individually, perhaps tacking on a semicolon at the end of the final query if omitted.

This solution is easy to write, SQL standard compliant, and just plain works. Not requiring the delimiter is a sure path to madness.

You could parse it yourself I suppose. Look for the keywords SELECT, DELETE, UPDATE, INSERT, EXEC, etc.

As you parse, if you encounter a "(" increment a counter: nest_level++

If you encounter a ")" decrement nest_level--

Then when you come across a keyword, and nest_level == 0, then you've come to the next statement.

You'll also have to handle cases like

 INSERT ...
 SELECT ....

So for an INSERT you would have to look for either SELECT or VALUES...

And no doubt other cases.

Agree with kquinn you should just require the semicolon. I don't think there's anything "uncool" about that.