CODEWITHSUNDEEP
c#regex
StealthRT
StealthRT

Reputation: 10542

C# regex separate sql query

Hey all I am wondering if anyone has a REGEX that can take a query like this:

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

And have it formatted like this:

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

I've been looking around and all I can find is finding an GO or something like that to start the split which wouldn't help me at all. Also, the select statement can have OR or more AND's in it as well. The above is just an example of one such query I would be running.

Upvotes: 0

Views: 2191

Answers (2)

Arturo Menchaca
Arturo Menchaca

Reputation: 15982

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'

Upvotes: 1

ndd
ndd

Reputation: 3071

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

Upvotes: 1

Related Questions