Split text into selects-CodePudding

Having a string read from a file having next content:

select 1 from dual;
select 2 from dual;
select 3 from dual;

I wanted to extract each select statement using next regexp:

Regex.Split(sqlS, @";\s \n")

The resulted array contains all selects, but the last one has the semi-colon at the end which is what I want to avoid. I would like to be able to split a text which contains many selects each of them ending with semi-colon. Each select start on a new line.

CodePudding user response：

Trim the ; from the end of the string before splitting with your regex (I think \s* will work better, even if the line endings are not CRLF):

Regex.Split( sqlS.TrimEnd(new[] {';'}), @";\s*\n" )

See the C# demo:

var text = @"select 1 from dual;
select 2 from dual;
select 3 from dual;";
var output = Regex.Split( text.TrimEnd(new[] {';'}), @";\s*\n" );
foreach (var s in output)
{
    Console.WriteLine(s);
}

Output:

select 1 from dual
select 2 from dual
select 3 from dual

CodePudding user response：

If all sql statements are in a single file separated by ; with new line, then you can try below code to get array of sql statement,

var filePath = @"C:\Program Files\sqlstatements.txt";

var sqlStatements = File.ReadAllLines(filePath)  //Read file and store all lines in an array
        .Select(x => x.Trim(';'))  //Now iterate over each line and remove semi-colon.
        .ToList();  //Optional: Convert String[] to List<string>