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;
}