Algorithm for formating SQL code

code formattingparsingsql

I need a tool (for in house usage) that will format SQL code (SQL Server/MySQL).
There are various 3rd party tools and online web sites that do it but no exactly how I need it.

So I want to write my own tool that will fit my needs.

First question is there any standard or a convention for how the SQL code should be formatted? (the tools that I tried format it differently)

The second question, how should I approach this task?
Should at first convert the sql query into some data structure like a Tree?

Best Answer

...is there any standard or a convention for how the SQL code should be formatted?

Standard, no. You can put an entire SQL statement on one line as far as an SQL parser is concerned.

Convention, sure there are lots. It depends whether you're trying to maximize changeability or minimize space. I've written SQL formatters for both cases.

I just used particular character combinations to tell me where to break the SQL statement.

Here's one example from a Java DB2 SQL formatter that I wrote. Another Java program generated the Java code. The SQL came directly from the SYSIBM tables.

protected void prepareIndex00Select(String codeFacl)
        throws SQLException {
    StringBuffer sb = new StringBuffer();
    sb.append("SELECT CODE_FACL, SEQ_FACL, FILLER_TOF ");
    sb.append("    , CODE_TOF, NAME_FACL, NAME_LENGTH ");
    sb.append("    , CODE_FMB, ID_NCIC_ORI, NBR_PRINTER_PREFIX ");
    sb.append("    , ID_PERSONNEL_OFC, COMPLEX_CODE ");
    sb.append("    , PHS_CODE, DESIG_FACL_GRP, IND_DESIG_AUTH ");
    sb.append("    , CODE_FACL_I_T, INTKEY_FACL, IND_CDM_SENTENCING ");
    sb.append("    , MAL_FEM_IND, DEL_AFTER, IND_INMATES ");
    sb.append("    , VALUE_SO_CPU_STD, VALUE_SO_CPU_DAY ");
    sb.append("    , CODE_CAT, VALUE_DCN, XIDBKEY ");
    sb.append("    , FACL_FK_REGN ");
    sb.append("  FROM ");
    sb.append(creator);
    sb.append(".FACL ");
    sb.append("  WHERE CODE_FACL = ? ");
    if (additionalSQL != null) sb.append(additionalSQL);

    psIndex00 = connection.prepareStatement(sb.toString());
    psIndex00.setString(1, codeFacl);

}   // End prepareIndex00Select method
Related Topic