Download... the code. (45 kb)

Have you ever gotten a SQL statement from someone that is, to put it mildly, difficult to decipher. Well, I have and was getting kind of tired of trying to make sense of the jumbled code. Given, the need is the mother of me getting off my butt, I wrote a little utility. It was actually so simple, I was actually surprised. Code is below. Note that the code uses the Microsoft.VisualBasic reference (yeah, I know, but it has the really useful case insensitive Replace function)

The before and after shot.

Add the following to a Class

private string MakeSqlPurty(string JumbledSql) 
{

string sPurtySql;

sPurtySql = JumbledSql;

//find and replace all the "," with carriage returns & commas
sPurtySql = sPurtySql.Replace(", ", ",");
sPurtySql = sPurtySql.Replace(",", ",\n\t");

//move FROM, WHERE, ORDER BY, GROUP BY down by 2 lines
sPurtySql = Strings.Replace (sPurtySql, " FROM ", "\n\nFROM ", 1, -1, CompareMethod.Text);
sPurtySql = Strings.Replace (sPurtySql, " WHERE ", "\n\nWHERE ", 1, -1, CompareMethod.Text);
sPurtySql = Strings.Replace (sPurtySql, " ORDER BY ", "\n\nORDER BY ", 1, -1, CompareMethod.Text);
sPurtySql = Strings.Replace (sPurtySql, " GROUP BY ", "\n\nGROUP BY ", 1, -1, CompareMethod.Text);
sPurtySql = Strings.Replace (sPurtySql, " VALUES", "\n\nVALUES", 1, -1, CompareMethod.Text);
sPurtySql = Strings.Replace (sPurtySql, " SET ", "\n\nSET ", 1, -1, CompareMethod.Text);

sPurtySql = Strings.Replace (sPurtySql, " and ", "\n\tand ", 1, -1, CompareMethod.Text);			
sPurtySql = Strings.Replace (sPurtySql, " or ", "\n\tor ", 1, -1, CompareMethod.Text);			

//change keywords to upper case
sPurtySql = Strings.Replace (sPurtySql, "SELECT ", "SELECT ", 1, -1, CompareMethod.Text);
sPurtySql = Strings.Replace (sPurtySql, "UPDATE ", "UPDATE ", 1, -1, CompareMethod.Text);
sPurtySql = Strings.Replace (sPurtySql, "DELETE ", "DELETE ", 1, -1, CompareMethod.Text);
sPurtySql = Strings.Replace (sPurtySql, "INSERT ", "INSERT ", 1, -1, CompareMethod.Text);

return sPurtySql;

}

Download... the code. (45 kb)