tree-sql-parser

C# library for parsing SQL select statements into an Abstract Syntax Tree (AST). The AST can be analysed, transformed and translated into various SQL dialects (SQL Server, Oracle, MySql (MariaDb), Sqlite, Postgres amd DB2 currently supported).

Available on Nuget, tree-sql-parser.

Install-Package tree-sql-parser

Code Samples

Parse SQL

using TreeSqlParser.Model;
using TreeSqlParser.Parsing;

string sql = "select id, surname from dbo.people";
var parser = new SelectParser();
SqlRootElement root = parser.ParseSelectStatement(sql);

Traverse AST explicitly

using TreeSqlParser.Model;
using TreeSqlParser.Model.Columns;
using TreeSqlParser.Model.Selects;

var statement = (SelectStatement)root.Child;
List<Column> columns = statement.Selects[0].Columns;

Traverse AST with Linq

using TreeSqlParser.Model;
using TreeSqlParser.Model.Columns;

var allElements = root.Flatten();
List<PrimitiveColumn> columns2 = allElements.OfType<PrimitiveColumn>().ToList();

Modify AST

using TreeSqlParser.Model;
using TreeSqlParser.Model.Columns;
using TreeSqlParser.Parsing;

// modify statement to: "select id, UPPER(surname) from dbo.people"
var nameColumn = root.Flatten().OfType<PrimitiveColumn>().Single(x => x.Name.Name == "surname");
var toUpperColumn = SelectParser.ParseColumn("UPPER(surname)");

// remove nameColumn from the AST and replace it with toUpperColumn
nameColumn.ReplaceSelf(toUpperColumn);

Generate SQL

FullSqlServerWriter has full support for converting any AST back into SQL.

using TreeSqlParser.Model;
using TreeSqlParser.Writers;

string fullSqlServerSql = SqlWriterFactory.FullSqlServerWriter().GenerateSql(root);

Generate SQL with CommonSqlWriters

CommonSqlWriters have more limited support, which can be translated to a variety of dialects. More dialects to follow.

using TreeSqlParser.Model;
using TreeSqlParser.Writers;

string commonSqlServerSql = SqlWriterFactory.CommonSqlWriter(SqlWriterType.SqlServer).GenerateSql(root);
string commonOracleSql = SqlWriterFactory.CommonSqlWriter(SqlWriterType.Oracle).GenerateSql(root);
string commonMySqlSql = SqlWriterFactory.CommonSqlWriter(SqlWriterType.MySql).GenerateSql(root);
string commonSqliteSql = SqlWriterFactory.CommonSqlWriter(SqlWriterType.Sqlite).GenerateSql(root);
string commonPostgresSql = SqlWriterFactory.CommonSqlWriter(SqlWriterType.Postgres).GenerateSql(root);
string commonMariaDbSql = SqlWriterFactory.CommonSqlWriter(SqlWriterType.MariaDb).GenerateSql(root);
string commonDb2ql = SqlWriterFactory.CommonSqlWriter(SqlWriterType.Db2).GenerateSql(root);

CommonSqlWriters support

SQL Feature	SQL Server	Oracle	MySql (MariaDB)	Sqlite	Postgres	DB2	Notes
Simple SELECT	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️	SELECT .. FROM .. WHERE .. GROUP BY .. HAVING .. ORDER BY
SET operations	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️	UNION, UNION ALL, INTERSECT, EXCEPT
SUBSELECT	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️
CTE	✔️	✔️	✔️	✔️	✔️	✔️	non recursive only
ARITHMETIC (+-*/%)	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️	+ is interpreted as plus. If you need to concatenate string use CONCAT(...). Oracle - % is converted to MOD(x, y)
JOINS: INNER, LEFT	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️
JOINS: RIGHT	✔️✔️	✔️✔️	✔️✔️	✔️	✔️✔️	✔️✔️	Sqlite - rewrites as LEFT JOIN
JOINS: FULL	✔️✔️	✔️✔️			✔️✔️	✔️✔️
FUNCTIONS	✔️	✔️	✔️	✔️	✔️	✔️	Limited set of known functions - see below
AGGREGATIONS	✔️	✔️	✔️	✔️	✔️	✔️	SUM, MIN, MAX, AVG, COUNT, COUNT DISTINCT only
GROUP BY	✔️	✔️	✔️	✔️	✔️	✔️	Simple columns only, no GROUPING SETS
TOP	✔️✔️						Use FETCH, OFFSET instead on other DBs
FETCH, OFFSET	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️	✔️✔️
CAST column	✔️	✔️	✔️	✔️	✔️	✔️	CAST as following types only: nvarchar, varchar, int, real, timestamp
CONVERT column	✔️	✔️	✔️	✔️	✔️	✔️	TRY_CONVERT not supported
BOOL column	✔️	✔️✔️	✔️	✔️	✔️✔️	✔️✔️	Rewrites as 1 or 0, where Bool is not directly supported

Function support is limited to the following list of known SQL Server functions. These get translated into native function calls on other SQL dialects where possible, or into complex statements where not.

Function	SQL Server	Oracle	MySql (MariaDB)	Sqlite	Postgres	DB2
Abs(num)	✔️	✔️	✔️	✔️	✔️	✔️
AddDays(date, days)	✔️	✔️	✔️	✔️	✔️	✔️
AddMonths(date, months)	✔️	✔️	✔️	✔️	✔️	✔️
AddYears(date, years)	✔️	✔️	✔️	✔️	✔️	✔️
Ceiling(num)	✔️	✔️	✔️	✔️	✔️	✔️
CharIndex(fullText, search)	✔️	✔️	✔️	✔️	✔️	✔️
CharIndex(fullText, search, startIndex)	✔️	✔️	✔️	✔️	✔️	✔️
Choose(selector, items... )	✔️	✔️	✔️	✔️	✔️	✔️
Coalesce(columns...)	✔️	✔️	✔️	✔️	✔️	✔️
Concat(columns...)	✔️	✔️	✔️	✔️	✔️	✔️
Concat_WS(seperator, columns...)	✔️	✔️	✔️	✔️	✔️	✔️
DateFromParts(year, month, day)	✔️	✔️	✔️	✔️	✔️	✔️
Day(date)	✔️	✔️	✔️	✔️	✔️	✔️
DaysBetween(date1, date2)	✔️	✔️	✔️	✔️	✔️	✔️
Exp(num)	✔️	✔️	✔️		✔️	✔️
Floor(num)	✔️	✔️	✔️	✔️	✔️	✔️
GetDate()	✔️	✔️	✔️	✔️	✔️	✔️
GetTimestamp()	✔️	✔️	✔️	✔️	✔️	✔️
IsNull(column)	✔️	✔️	✔️	✔️	✔️	✔️
Left(text)	✔️	✔️	✔️	✔️	✔️	✔️
Len(text)	✔️	✔️	✔️	✔️	✔️	✔️
Log(num)	✔️	✔️	✔️		✔️	✔️
Log(num, base)	✔️	✔️	✔️		✔️	✔️
Lower(text)	✔️	✔️	✔️	✔️	✔️	✔️
LTrim(text)	✔️	✔️	✔️	✔️	✔️	✔️
Month(date)	✔️	✔️	✔️	✔️	✔️	✔️
MonthsBetween(date1, date2)	✔️	✔️	✔️	✔️	✔️	✔️
NullIf(column)	✔️	✔️	✔️	✔️	✔️	✔️
Power(num, pwr)	✔️	✔️	✔️		✔️	✔️
Replace(text)	✔️	✔️	✔️	✔️	✔️	✔️
Replicate(text, n)	✔️	✔️	✔️	✔️	✔️	✔️
Reverse(text)	✔️	✔️	✔️		✔️	✔️
Right(text, n)	✔️	✔️	✔️	✔️	✔️	✔️
Round(num)	✔️	✔️	✔️	✔️	✔️	✔️
RTrim(text)	✔️	✔️	✔️	✔️	✔️	✔️
Sign(num)	✔️	✔️	✔️	✔️	✔️	✔️
Space(n)	✔️	✔️	✔️	✔️	✔️	✔️
Substring(text, start, count)	✔️	✔️	✔️	✔️	✔️	✔️
ToChar(column)	✔️	✔️	✔️	✔️	✔️	✔️
ToTimestamp(column)	✔️	✔️	✔️	✔️	✔️	✔️
ToNumber(column)	✔️	✔️	✔️	✔️	✔️	✔️
Trim(text)	✔️	✔️	✔️	✔️	✔️	✔️
TruncateDate(date)	✔️	✔️	✔️	✔️	✔️	✔️
Upper(text)	✔️	✔️	✔️	✔️	✔️	✔️
Year(date)	✔️	✔️	✔️	✔️	✔️	✔️
YearsBetween(date1, date2)	✔️	✔️	✔️	✔️	✔️	✔️