public List <Tuple <string, FieldAggregationMethod?> > FindColumnNamesInCalculatedFieldWithAggregationMethod(string fieldName)
{
const string pattern = @"(MIN\(|MAX\(|AVG\(|SUM\()*[\[a-zA-z@\]][^ ()+\-*\\/%,]+"; // a-z without punctuation or Min(field, MAX(field, AVG(field, SUM(field
var regex = new Regex(pattern);
var matches = regex.Matches(fieldName);
var distinctMatches = matches.Cast <Match>().Select(match => match.Value).Distinct().ToList();
var result = new List <Tuple <string, FieldAggregationMethod?> >();
foreach (var r in distinctMatches)
{
var key = r;
string agg = null;
FieldAggregationMethod?aggregate = null;
if (r.Contains("("))
{
agg = r.Split('(')[0];
key = r.Split('(')[1];
}
if (SqlStopWords.Contains(key) ||
key == "_C" ||
key.StartsWith("dbo.") ||
key.StartsWith("@"))
{
continue;
}
if (agg != null)
{
switch (agg.ToLower())
{
case "min": aggregate = FieldAggregationMethod.Min;
break;
case "max": aggregate = FieldAggregationMethod.Max;
break;
case "sum": aggregate = FieldAggregationMethod.Sum;
break;
case "avg": aggregate = FieldAggregationMethod.Average;
break;
}
}
result.Add(new Tuple <string, FieldAggregationMethod?>(key, aggregate));
}
return(result);
}
public List <string> FindColumnNamesInCalculatedField(string fieldName)
{
const string pattern = @"[\[a-zA-z@\]][^ ()+\-*\\/%,]+"; // a-z without punctuation
var regex = new Regex(pattern);
var matches = regex.Matches(fieldName);
var result = matches.Cast <Match>().Select(match => match.Value).Distinct().ToList();
result = result.Where(x =>
!SqlStopWords.Contains(x) &&
x != "_C" &&
!x.StartsWith("dbo.") &&
!x.StartsWith("@")).ToList();
return(result);
}
