C# regex separate sql query

Can you please state what is your purpose to format SQL query? There are so many online as well as offline tools available to format SQL query. So I am wondering why you want to do it using Regex :)

Like

• http://www.sql-format.com/
• https://github.com/sqlparser/isf_notepad
• http://poorsql.com/

You can create a regex expression to captures the SELECT, FROM and WHERE parts into groups and use Regex.Replace() method to creates a new string like this one:

SELECT pillers, Balloons, Tacks 
FROM the_database_file 
WHERE Balloons != 'small' AND Balloons != 'large'

This is the code:

var sql = "SELECT pillers, Balloons, Tacks FROM the_database_file WHERE Balloons != 'small' AND Balloons != 'large'";
string pattern = "^(?<select>SELECT\\s+[\\w\\*\\.]+(?:[\\s\\,]+[\\w\\*\\.]+)*)\\s+(?<from>FROM\\s+\\w+)\\s+(?<where>(WHERE\\s+.+?)(\\s+(?:AND|OR)\\s+.+?)*)$";            
var result = Regex.Replace(sql, pattern, "${select}\n${from}\n${where}");

I think you can't create a line for each condition because you have no way to get all matches of the same capturing group.

But you can do something like this:

var sql = "SELECT pillers, Balloons, Tacks FROM the_database_file WHERE Balloons != 'small' AND Balloons != 'large'";

string pattern = "^(?<select>SELECT\\s+[\\w\\*\\.]+(?:[\\s\\,]+[\\w\\*\\.]+)*)\\s+(?<from>FROM\\s+\\w+)\\s+(?<where>(WHERE\\s+.+?)(\\s+(?:AND|OR)\\s+.+?)*)$";

Regex regex = new Regex(pattern);
var match = regex.Match(sql);

var all = match.Groups[0];
var select = match.Groups["select"];
var from = match.Groups["from"];
var where = match.Groups["where"];

var conditions = match.Groups.Cast<Group>().Except(new Group[] { all, select, from, where });

string result = string.Format("{0}\n{1}\n{2}", select.Value, from.Value, string.Join("\n", conditions.Select(c => c.Value.Trim())));

Console.WriteLine(result);

You can exclude from the groups the first one, the SELECT, the FROM and the WHERE groups, and the remaining groups will be the query conditions.

And this is what you get:

SELECT pillers, Balloons, Tacks
FROM the_database_file
WHERE Balloons != 'small'
AND Balloons != 'large'