using System;
using System.Text.RegularExpressions;
namespace GB5Shared.DBQueryConverter
{
public static class ConvertSqlToPostgres
{
///
/// Convert a SQL Server statement to PostgreSQL-compatible SQL.
/// Handles functions, types, joins, TOP/LIMIT, date functions, string functions, ISNULL -> COALESCE, and many common cases.
/// NOTE: Highly robust but not a perfect parser — some ambiguous constructs (e.g. '+' used for numeric addition vs string concat)
/// may need manual review. Those places are minimized and commented.
///
public static string ConvertSqlServerToPostgres(string sql)
{
if (string.IsNullOrWhiteSpace(sql)) return sql;
string pg = sql;
pg = Regex.Replace(pg,
@"\b((is|has|can|enable|active|status)[A-Za-z0-9_]*)\s*=\s*0\b",
"$1 = false", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg,
@"\b((is|has|can|enable|active|status)[A-Za-z0-9_]*)\s*=\s*1\b",
"$1 = true", RegexOptions.IgnoreCase);
// Generic isXXXX pattern
pg = Regex.Replace(pg, @"\b(is[A-Za-z0-9_]*)\s*=\s*0\b", "$1 = false", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\b(is[A-Za-z0-9_]*)\s*=\s*1\b", "$1 = true", RegexOptions.IgnoreCase);
// =====================================================
// FUNCTIONS
// =====================================================
pg = Regex.Replace(pg, @"GETDATE\s*\(\s*\)", "NOW()", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"ISNULL\s*\(", "COALESCE(", RegexOptions.IgnoreCase);
// CONVERT(VARCHAR, X) → CAST(X as TEXT)
pg = Regex.Replace(pg,
@"CONVERT\s*\(\s*VARCHAR\s*\(\d+\)\s*,\s*(.*?)\)",
"CAST($1 AS TEXT)", RegexOptions.IgnoreCase);
// CAST(x as NVARCHAR(xx)) → CAST(x as TEXT)
pg = Regex.Replace(pg,
@"CAST\s*\((.*?)\s+AS\s+NVARCHAR\s*\(\d+\)\)",
"CAST($1 AS TEXT)", RegexOptions.IgnoreCase);
// =====================================================
// SELECT TOP → REMOVE TOP
// =====================================================
pg = Regex.Replace(pg, @"SELECT\s+TOP\s+\d+\s+", "SELECT ", RegexOptions.IgnoreCase);
// =====================================================
// DATA TYPES
// =====================================================
pg = Regex.Replace(pg, @"NVARCHAR\s*\(\s*MAX\s*\)", "TEXT", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"VARCHAR\s*\(\s*MAX\s*\)", "TEXT", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"DATETIME\b", "TIMESTAMP", RegexOptions.IgnoreCase);
// IDENTITY → SERIAL
pg = Regex.Replace(pg,
@"INT\s+IDENTITY\s*\(\s*1\s*,\s*1\s*\)",
"SERIAL", RegexOptions.IgnoreCase);
// =====================================================
// REMOVE SQL SERVER BRACKETS
// =====================================================
pg = pg.Replace("[", "").Replace("]", "");
// =====================================================
// REMOVE NOLOCK
// =====================================================
pg = Regex.Replace(pg, @"WITH\s*\(NOLOCK\)", "", RegexOptions.IgnoreCase);
// =====================================================
// COMMA JOIN → CROSS JOIN
// =====================================================
pg = Regex.Replace(pg,
@"FROM\s+(\w+)\s*,\s*(\w+)",
"FROM $1 CROSS JOIN $2",
RegexOptions.IgnoreCase);
// Normalize whitespace a bit to simplify regexes
pg = pg.Replace("\r\n", " ").Replace("\n", " ").Trim();
// --------------------------
// Basic function/type replacements
// --------------------------
pg = Regex.Replace(pg, @"\bGETUTCDATE\s*\(\s*\)", "NOW()", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bGETDATE\s*\(\s*\)", "NOW()", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bCURRENT_TIMESTAMP\b", "NOW()", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bISNULL\s*\(", "COALESCE(", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bNEWID\s*\(\s*\)", "gen_random_uuid()", RegexOptions.IgnoreCase);
// LEN -> LENGTH
pg = Regex.Replace(pg, @"\bLEN\s*\(", "LENGTH(", RegexOptions.IgnoreCase);
// LTRIM(RTRIM(x)) or RTRIM(LTRIM(x)) => TRIM(x)
pg = Regex.Replace(pg, @"\bLTRIM\s*\(\s*RTRIM\s*\(", "TRIM(", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bRTRIM\s*\(\s*LTRIM\s*\(", "TRIM(", RegexOptions.IgnoreCase);
// LTRIM(x) -> LTRIM(x) and RTRIM(x) -> RTRIM(x) are supported by PG; later replace LTRIM/RTRIM combos handled above.
// NVARCHAR(MAX) / VARCHAR(MAX) / NTEXT -> TEXT
pg = Regex.Replace(pg, @"\bNVARCHAR\s*\(\s*MAX\s*\)", "TEXT", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bVARCHAR\s*\(\s*MAX\s*\)", "TEXT", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bNTEXT\b", "TEXT", RegexOptions.IgnoreCase);
// DATETIME / SMALLDATETIME -> TIMESTAMP
pg = Regex.Replace(pg, @"\bSMALLDATETIME\b", "TIMESTAMP", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bDATETIME\b", "TIMESTAMP", RegexOptions.IgnoreCase);
// BIT -> BOOLEAN (note: data migration required separately)
pg = Regex.Replace(pg, @"\bBIT\b", "BOOLEAN", RegexOptions.IgnoreCase);
// IDENTITY -> SERIAL (simple common case)
pg = Regex.Replace(pg, @"\bINT\s+IDENTITY\s*\(\s*\d+\s*,\s*\d+\s*\)", "SERIAL", RegexOptions.IgnoreCase);
// != -> <>
pg = pg.Replace("!=", "<>");
pg = Regex.Replace(pg, @"\bOUTER\s+APPLY\b", "LEFT JOIN LATERAL", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bCROSS\s+APPLY\b", "CROSS JOIN LATERAL", RegexOptions.IgnoreCase);
// Remove WITH (NOLOCK) hints
pg = Regex.Replace(pg, @"\bWITH\s*\(\s*NOLOCK\s*\)", "", RegexOptions.IgnoreCase);
// Remove square-bracket quoting [col] -> "col" or just col (we'll remove brackets to be safe)
// NOTE: If you used case-sensitive quoted identifiers, manual fix may be required.
pg = pg.Replace("[", "").Replace("]", "");
// --------------------------
// TOP N -> LIMIT N
// --------------------------
// Extract TOP n, remove it from SELECT and append LIMIT n at the end.
var topMatch = Regex.Match(pg, @"SELECT\s+TOP\s+(\d+)\s+", RegexOptions.IgnoreCase);
if (topMatch.Success)
{
string n = topMatch.Groups[1].Value;
pg = Regex.Replace(pg, @"SELECT\s+TOP\s+\d+\s+", "SELECT ", RegexOptions.IgnoreCase);
// If existing LIMIT present, don't append another
if (!Regex.IsMatch(pg, @"\bLIMIT\b", RegexOptions.IgnoreCase))
{
pg = pg.TrimEnd().TrimEnd(';') + $" LIMIT {n}";
}
}
// --------------------------
// Convert old-style joins: FROM A, B WHERE A.col = B.col -> FROM A JOIN B ON A.col = B.col
// This is heuristic — only convert simple two-table comma joins with immediate equality.
// --------------------------
pg = Regex.Replace(pg,
@"FROM\s+([A-Za-z0-9_]+)\s*,\s*([A-Za-z0-9_]+)\s+WHERE\s+([A-Za-z0-9_\.]+)\s*=\s*([A-Za-z0-9_\.]+)\s",
"FROM $1 JOIN $2 ON $3 = $4 ",
RegexOptions.IgnoreCase);
// --------------------------
// CONCAT(...) -> a || b || ...
// --------------------------
pg = Regex.Replace(pg, @"\bCONCAT\s*\(", m =>
{
// convert CONCAT(a,b,c) to (a || b || c)
string inside = ExtractParenthesisContents(m.Index + m.Length - 1, pg);
if (inside == null) return m.Value; // fallback
var parts = SplitCsvTopLevel(inside);
string joined = string.Join(" || ", parts);
return "(" + joined + ")";
}, RegexOptions.IgnoreCase);
// --------------------------
// LEFT(col, n) / RIGHT(col, n) -> SUBSTRING equivalents
// LEFT(col, n) -> SUBSTRING(col FROM 1 FOR n)
// RIGHT(col, n) -> SUBSTRING(col FROM (LENGTH(col)-n+1) FOR n)
// --------------------------
pg = Regex.Replace(pg, @"\bLEFT\s*\(\s*([^,]+?)\s*,\s*([^\)]+?)\s*\)",
"SUBSTRING($1 FROM 1 FOR $2)", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bRIGHT\s*\(\s*([^,]+?)\s*,\s*([^\)]+?)\s*\)",
"SUBSTRING($1 FROM (LENGTH($1) - ($2) + 1) FOR $2)", RegexOptions.IgnoreCase);
// --------------------------
// IIF(cond, a, b) -> CASE WHEN cond THEN a ELSE b END
// --------------------------
pg = Regex.Replace(pg, @"\bIIF\s*\(\s*(.+?)\s*,\s*(.+?)\s*,\s*(.+?)\s*\)",
"CASE WHEN $1 THEN $2 ELSE $3 END", RegexOptions.IgnoreCase | RegexOptions.Singleline);
// --------------------------
// DATEADD(unit, n, date) -> date + (n * INTERVAL '1 unit')
// We'll use a MatchEvaluator to correctly map common units (year, month, day, hour, minute, second)
// --------------------------
pg = Regex.Replace(pg, @"\bDATEADD\s*\(\s*([A-Za-z']+)\s*,\s*([^\),]+?)\s*,\s*([^\)]+?)\s*\)",
new MatchEvaluator(DateAddEvaluator), RegexOptions.IgnoreCase);
// --------------------------
// DATEDIFF(unit, a, b) -> implement per-unit versions
// Year, Month, Day, Hour, Minute, Second handled
// --------------------------
pg = Regex.Replace(pg, @"\bDATEDIFF\s*\(\s*([A-Za-z']+)\s*,\s*([^\),]+?)\s*,\s*([^\)]+?)\s*\)",
new MatchEvaluator(DateDiffEvaluator), RegexOptions.IgnoreCase);
// --------------------------
// DATEPART(field, date) -> EXTRACT(field FROM date)
// --------------------------
pg = Regex.Replace(pg, @"\bDATEPART\s*\(\s*([A-Za-z]+)\s*,\s*([^\)]+?)\s*\)",
"EXTRACT($1 FROM $2)", RegexOptions.IgnoreCase);
// --------------------------
// CONVERT / CAST conversions
// CONVERT(VARCHAR(30), col, 121) -> TO_CHAR(col, 'YYYY-MM-DD HH24:MI:SS')
// Simplified mapping for common styles; fallback to CAST(col AS TEXT)
// --------------------------
pg = Regex.Replace(pg, @"\bCONVERT\s*\(\s*([A-Za-z0-9_\(\)\s]+)\s*,\s*([^\),]+?)\s*(?:,\s*(\d+)\s*)?\)",
new MatchEvaluator(ConvertEvaluator), RegexOptions.IgnoreCase);
// CAST(some AS NVARCHAR(MAX)) -> CAST(some AS TEXT)
pg = Regex.Replace(pg, @"\bCAST\s*\(\s*([^\s]+)\s+AS\s+NVARCHAR\s*\(\s*MAX\s*\)\s*\)",
"CAST($1 AS TEXT)", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"\bCAST\s*\(\s*([^\s]+)\s+AS\s+VARCHAR\s*\(\s*MAX\s*\)\s*\)",
"CAST($1 AS TEXT)", RegexOptions.IgnoreCase);
// --------------------------
// String concatenation with + : CAUTION
// Only convert obvious cases where one side is a string literal OR CONCAT usage missed:
// e.g. 'abc' + col OR col + 'abc'
// We intentionally avoid converting numeric + numeric or (col+col) where type ambiguous.
// --------------------------
pg = Regex.Replace(pg, @"('(?:[^']|'')*')\s*\+\s*([A-Za-z0-9_\.]+)", "$1 || $2", RegexOptions.IgnoreCase);
pg = Regex.Replace(pg, @"([A-Za-z0-9_\.]+)\s*\+\s*('(?:[^']|'')*')", "$1 || $2", RegexOptions.IgnoreCase);
// --------------------------
// Convert SQL Server style TOP in parentheses or other oddities already handled above.
// --------------------------
// Final cleanup: multiple spaces -> single, trim
pg = Regex.Replace(pg, @"\s{2,}", " ").Trim();
return pg;
}
// --------------------------
// Helper: DATEADD evaluator
// --------------------------
private static string DateAddEvaluator(Match m)
{
// m.Groups: 1=unit, 2=number, 3=date
string unitRaw = m.Groups[1].Value.Trim('\'', ' ', '"');
string num = m.Groups[2].Value.Trim();
string dateExpr = m.Groups[3].Value.Trim();
string unit = MapDateUnit(unitRaw);
// If unit mapping empty, fallback to original pattern replacement (best-effort)
if (string.IsNullOrEmpty(unit))
{
return $"({dateExpr} + ({num}) * INTERVAL '1 {unitRaw}')";
}
// Use INTERVAL '1 unit' multiplied by num
// Make sure to preserve arithmetic (don't cast aggressively)
return $"({dateExpr} + ({num}) * INTERVAL '1 {unit}')";
}
// --------------------------
// Helper: DATEDIFF evaluator
// --------------------------
private static string DateDiffEvaluator(Match m)
{
// m.Groups: 1=unit, 2=a, 3=b (DATEDIFF(unit, a, b))
string unitRaw = m.Groups[1].Value.Trim('\'', ' ', '"').ToLowerInvariant();
string a = m.Groups[2].Value.Trim();
string b = m.Groups[3].Value.Trim();
switch (unitRaw)
{
case "year":
case "yy":
case "yyyy":
// simple year difference
return $"(DATE_PART('year', {b}) - DATE_PART('year', {a}))::INT";
case "month":
case "mm":
case "m":
// months difference
return $"(((DATE_PART('year', {b}) - DATE_PART('year', {a})) * 12) + (DATE_PART('month', {b}) - DATE_PART('month', {a})))::INT";
case "day":
case "dd":
case "d":
// total days difference using epoch
return $"CAST(EXTRACT(EPOCH FROM ({b}) - ({a})) / 86400 AS INTEGER)";
case "hour":
case "hh":
return $"CAST(EXTRACT(EPOCH FROM ({b}) - ({a})) / 3600 AS INTEGER)";
case "minute":
case "mi":
case "n":
return $"CAST(EXTRACT(EPOCH FROM ({b}) - ({a})) / 60 AS INTEGER)";
case "second":
case "ss":
case "s":
return $"CAST(EXTRACT(EPOCH FROM ({b}) - ({a})) AS INTEGER)";
default:
// fallback: return epoch seconds difference (user can divide later)
return $"CAST(EXTRACT(EPOCH FROM ({b}) - ({a})) AS INTEGER)";
}
}
// --------------------------
// Helper: CONVERT evaluator
// --------------------------
// Handles: CONVERT(type, expr, style)
// Common style 121 -> 'YYYY-MM-DD HH24:MI:SS'
// If type is character-like and style present, map to TO_CHAR
private static string ConvertEvaluator(Match m)
{
// groups: 1 = type (e.g. VARCHAR(30)), 2 = expr, 3 = optional style number
string typePart = m.Groups[1].Value.Trim();
string expr = m.Groups[2].Value.Trim();
string style = m.Groups[3].Success ? m.Groups[3].Value.Trim() : null!;
// if style specified and expr is a datetime expression, map few known styles
if (!string.IsNullOrEmpty(style))
{
if (style == "121" || style == "120")
{
// ODBC canonical: 'yyyy-mm-dd hh:mi:ss'
return $"TO_CHAR({expr}, 'YYYY-MM-DD HH24:MI:SS')";
}
// Add other style mappings here if needed
}
// If target type is some text type, cast to TEXT
if (Regex.IsMatch(typePart, @"\bVARCHAR\b|\bCHAR\b|\bNVARCHAR\b|\bNCHAR\b|\bTEXT\b", RegexOptions.IgnoreCase))
{
return $"CAST({expr} AS TEXT)";
}
// If converting to INT or numeric
if (Regex.IsMatch(typePart, @"\bINT\b|\bBIGINT\b|\bSMALLINT\b|\bTINYINT\b", RegexOptions.IgnoreCase))
{
return $"CAST({expr} AS INTEGER)";
}
// Fallback: wrap as CAST(expr AS typePart) — remove NVARCHAR(MAX) etc handled earlier
return $"CAST({expr} AS {typePart})";
}
// --------------------------
// Helper: Map SQL Server date units to PostgreSQL interval unit words
// --------------------------
private static string MapDateUnit(string unitRaw)
{
if (string.IsNullOrEmpty(unitRaw)) return null!;
string u = unitRaw.Trim().ToLowerInvariant();
switch (u)
{
case "year":
case "yy":
case "yyyy":
return "year";
case "month":
case "mm":
case "m":
return "month";
case "day":
case "dd":
case "d":
return "day";
case "hour":
case "hh":
return "hour";
case "minute":
case "mi":
case "n":
return "minute";
case "second":
case "ss":
case "s":
return "second";
default:
return unitRaw; // best-effort
}
}
// --------------------------
// Utility: Extract parenthesis contents starting at an index (where the '(' is)
// Returns null if unbalanced
// --------------------------
private static string ExtractParenthesisContents(int startIndex, string input)
{
if (startIndex < 0 || startIndex >= input.Length || input[startIndex] != '(') return null;
int depth = 0;
for (int i = startIndex; i < input.Length; i++)
{
if (input[i] == '(') depth++;
else if (input[i] == ')')
{
depth--;
if (depth == 0)
{
// contents between startIndex+1 and i-1
return input.Substring(startIndex + 1, i - startIndex - 1);
}
}
}
return null!; // unbalanced
}
// --------------------------
// Utility: split CSV at top-level (not splitting commas inside parentheses)
// --------------------------
private static string[] SplitCsvTopLevel(string s)
{
if (s == null) return new string[0];
var parts = new System.Collections.Generic.List();
int depth = 0;
int last = 0;
for (int i = 0; i < s.Length; i++)
{
char c = s[i];
if (c == '(') depth++;
else if (c == ')') depth--;
else if (c == ',' && depth == 0)
{
parts.Add(s.Substring(last, i - last).Trim());
last = i + 1;
}
}
parts.Add(s.Substring(last).Trim());
return parts.ToArray();
}
}
}