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