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