Convert ANSI SQL to T-SQL

gistfile1.md

One of the major challenges you may face is converting "normal" SQL to T-SQL which is Microsoft's dialect of SQL. I couldn't find any easy way to do this, however in doing some other work I found that Apache Calcite can actually perform this function quite simply. So... here's some code that does exactly that!

import org.apache.calcite.config.Lex;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;

public class SqlConverters {
  public static final SqlParser.Config DEFAULT_CONFIGURATION = SqlParser.configBuilder()
    .setCaseSensitive(true)
    .setLex(Lex.MYSQL)
    .build();

  public static final SqlParser.Config MSSQL_CONFIGURATION = SqlParser.configBuilder()
    .setCaseSensitive(true)
    .setLex(Lex.SQL_SERVER)
    .build();

  /**
   * Converts a given SQL query to T-SQL compatible with MSSQL databases.  Returns
   * null if the original query is not valid.
   * @param sql An ANSI SQL statement
   * @return A T-SQL representation of the original query
   */
  public static String convertToTSQL(String sql) {
    try {
      SqlNode node = SqlParser.create(sql, DEFAULT_CONFIGURATION).parseQuery();
      return node.toSqlString(SqlDialect.DatabaseProduct.MSSQL.getDialect()).getSql();
    } catch (SqlParseException e) {
      // Do nothing...
    }
    return null;
  }

  /**
   * Converts a given SQL query from T-SQL compatible to MySQL databases.  Returns
   * null if the original query is not valid.
   * @param sql An T-SQL SQL statement
   * @return A MySQL representation of the original query
   */
  public static String convertFromTSQL(String sql) {
    try {
      SqlNode node = SqlParser.create(sql, MSSQL_CONFIGURATION).parseQuery();
      return node.toSqlString(SqlDialect.DatabaseProduct.MYSQL.getDialect()).getSql();
    } catch (SqlParseException e) {
      // Do nothing...
    }
    return null;
  }
}