I inherited an ASP.NET app that I updated today.  One update was changing a SQL varchar field stored as numbers to letters, as in “1,2,3” to “A,B,C.”  (I'm saying its inherited because I probably wouldn't have stored comma-separated values in a single field had I designed the app.)

As numbers, the following worked fine: “...where code in (1,5,7).“

string sql = "SELECT * FROM table WHERE code IN (" + dr["code"] + ")";

But as letters it spit out an Invalid column name 'G' exception.  I needed to convert the “A,B,C“ string to 'A','B','C'.

This solution isn't clever, and as usual I would bet I get recommendations on how to improve it.  I'm posting it only because it was a 10 minute (at most) coding fix which in the not too distant past would have taken me a lot longer to do.  I can only attribute the decreased coding time to .NET, the economy of the C# syntax, and I'm a lucky cus who gets to write a lot of code.

string sql = "SELECT * FROM table WHERE code IN (" + GetCode(dr["code"].ToString()) + ")";

private string GetCode(string actions)
{
  // actions string = “A, B, C“
 string s = actions.Replace(" ","");
 string m = "";
 string[] arraychars = s.Split(",".ToCharArray());
 for (int i = 0 ; i < arraychars.Length ; i++)
 {
  if (i > 0)
   m += ",'" + arraychars[i].ToString() + "'";
  else
   m += "'" + arraychars[i].ToString() + "'";
 }
 return m;  // returns 'A','B','C'
}
 

[UPDATE]  I knew I'd get improvement suggestions on this.  Now instead of being happy about a 10 minute code fix, I SHOULD have been happy about a 90 second fix!  The updated function now consists of a single line.  Excellent, Jerry!

private string GetActionChars(string actions)
{
 return "'" + String.Join("', '", actions.Replace(" ", "").Split(',')) + "'";
}