/// <summary>
/// Splits the given <paramref name="sql" /> into <paramref name="parts" />;
/// </summary>
/// <param name="sql">The SQL to split.</param>
/// <param name="parts">The SQL parts.</param>
/// <returns><c>True</c> if the SQL could be split; else, <c>False</c>.</returns>
public bool SplitSQL(string sql, out SQLParts parts)
{
parts.Sql = sql;
parts.SqlSelectRemoved = null;
parts.SqlCount = null;
parts.SqlOrderBy = null;
// Extract the columns from "SELECT <whatever> FROM"
var m = RegexColumns.Match(sql);
if (!m.Success)
{
return(false);
}
// Save column list and replace with COUNT(*)
var g = m.Groups[1];
parts.SqlSelectRemoved = sql.Substring(g.Index);
if (RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
}
else
{
parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);
}
// Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
m = SimpleRegexOrderBy.Match(parts.SqlCount);
if (m.Success)
{
g = m.Groups[0];
parts.SqlOrderBy = g + parts.SqlCount.Substring(g.Index + g.Length);
parts.SqlCount = parts.SqlCount.Substring(0, g.Index);
}
return(true);
}
/// <summary>
/// Splits the given <paramref name="sql" /> into <paramref name="parts" />;
/// </summary>
/// <param name="sql">The SQL to split.</param>
/// <param name="parts">The SQL parts.</param>
/// <returns><c>True</c> if the SQL could be split; else, <c>False</c>.</returns>
public bool SplitSQL(string sql, out SQLParts parts)
{
parts.Sql = sql;
parts.SqlSelectRemoved = null;
parts.SqlCount = null;
parts.SqlOrderBy = null;
// Extract the columns from "SELECT <whatever> FROM"
var m = RegexColumns.Match(sql);
if (!m.Success)
{
return(false);
}
// Save column list and replace with COUNT(*)
var g = m.Groups[1];
parts.SqlSelectRemoved = sql.Substring(g.Index);
if (RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
var txt = m.Groups[1].ToString().Trim();
if (txt.StartsWith("Distinct", System.StringComparison.CurrentCultureIgnoreCase) && txt.Contains(",") == false)
{
parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
}
else if (txt.Contains(","))
{
m = SimpleRegexOrderBy.Match(sql);
if (m.Success)
{
g = m.Groups[0];
parts.SqlOrderBy = g + sql.Substring(g.Index + g.Length);
parts.SqlCount = $"SELECT COUNT(*) FROM ({sql.Substring(0, g.Index)}) __toolgood__ ";
return(true);
}
else
{
parts.SqlCount = $"SELECT COUNT(*) FROM ({sql}) __toolgood__ ";
}
}
else
{
parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
}
}
else
{
parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);
}
// Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
m = SimpleRegexOrderBy.Match(parts.SqlCount);
if (m.Success)
{
g = m.Groups[0];
parts.SqlOrderBy = g + parts.SqlCount.Substring(g.Index + g.Length);
parts.SqlCount = parts.SqlCount.Substring(0, g.Index);
}
return(true);
}