How to split mysql commands by ; excluding splitting when ; is between apex?

Question

In a rich text box i have differents string such as :

                              show tables;
                              show database;
                              show status;

So if i want to do execute all command I'll create one array string doing

                       string [] commands = richtextbox.Text.Split(';');

now in commands I'll have :

                         commands[0] = "show tables";
                         commands[1] = "show databases";
                         commands[2] = "show status";

And it works fine! The problem is the following:

                              show database;
                              insert into table_x values ("string;s","id_s",1);
                              insert into table2_x values ("s;s",1);

Now if I'll do the split I'll break the second and third command. I'm thinking about regular expression but how can apply to the split function? How can I fix that? Thanks in advance.

do you have more than one command in one line or all that commands are in separate lines ? — Selman Genç, Jun 08 '14 at 00:20
FYI, added explanation for the regex used in the C# demo. :) — zx81, Jun 08 '14 at 00:43

Casimir et Hippolyte · Accepted Answer · 2014-06-08T01:04:34.767

Instead of split use Regex.Matches(input, pattern) with this pattern to skip the content between quotes:

@"(?>[^""';]+|""(?>[^""]+|"""")*""|'(?>[^']+|'')*')+"

A working example:

using System;
using System.Text.RegularExpressions;
using System.Collections.Generic;

public class Test
{
    public static void Main()
    {
        string pattern = @"(?>[^""';]+|""(?>[^""]+|"""")*""|'(?>[^']+|'')*')+";
        string input = @"show tables;
                         insert into table_x values (""string;s"",""id_s"",1);
                         insert into table2_x values (""s;s"",1);
                         insert into table2_x values ('s'';s',1);";

        List<string> list = new List<string>();

        foreach (Match m in Regex.Matches(input, pattern)) {
            list.Add(m.Value.Trim());
        }
        string[] commands = list.ToArray();

        foreach (string s in commands) {
            Console.WriteLine(s);
        }
    }
}

so how can I do from code? I have to build an array of string with each field has one command. Can i do string []x = Regex.Matches(input, pattern); ?? — Andrea Rossi, Jun 08 '14 at 00:30
You must loop to the matchCollection, you can take a look at the doc for details: http://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex.matches(v=vs.110).aspx — Casimir et Hippolyte, Jun 08 '14 at 00:31

score 0 · Answer 2 · answered Jun 08 '14 at 00:31

This regex pattern should do the trick :

string input = "show database;" +
               "insert into table_x values (\"string;s\",\"id_s\",1);" +
               "insert into table2_x values (\"s;s\",1);";
string[] commands = Regex.Split(input, "(?<=^([^\"\r\n]|\"([^\"\\\\\r\n]|\\\\.)*\")*);", RegexOptions.ExplicitCapture);

As debuggex will explain it better than i could do :

(?<=^([^"\r\n]|"([^"\\\r\n]|\\.)*")*);

Regular expression visualization

score 0 · Answer 3 · edited May 23 '17 at 11:49

You can use this short regex:

"[^"]*"|((?=;))

The beauty of this regex is that it is easy to understand and to maintain if the need later arises.

This situation sounds very similar to "regex-match a pattern unless...". The left side of the alternation | matches complete "quoted strings". We will ignore these matches. The right side matches and captures positions preceding semicolos ; to Group 1, and we know they are the right positions because they were not matched by the expression on the left.

This program shows how to use the regex (see the results at the bottom of the online demo):

Output

*** HERE ARE THE SPLIT STRINGS ***
show database; 
insert into table_x values ("string;s","id_s",1);
insert into table2_x values ("s;s",1);

Sample Code:

using System;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
class Program
{
static void Main()  {
string s1 = @"
show database;
insert into table_x values (""string;s"",""id_s"",1);
insert into table2_x values (""s;s"",1);";
var myRegex = new Regex(@"""[^""]*""|((?=;))");
string replaced = myRegex.Replace(s1, delegate(Match m) {
    if (m.Groups[1].Value == "") return m.Value;
    else return "SplitHere";
    });
string[] splits = Regex.Split(replaced,"SplitHere");
foreach (string split in splits) Console.WriteLine(split);
Console.WriteLine("\nPress Any Key to Exit.");
Console.ReadKey();
} // END Main
} // END Program

Reference

How to match (or replace) a pattern except in situations s1, s2, s3...

How to split mysql commands by ; excluding splitting when ; is between apex?

3 Answers3