Skip to content

Format (pretty-print) SQL

This page is a recipe book for the SQL formatter that ships inside General SQL Parser. If you just want to take messy SQL and pretty-print it, copy the minimum example below and you are done. Read on only when you need to change how the output looks.

Every output on this page is real program output

Each recipe was run in its own process against Java 4.1.6 and .NET 4.1.0.7, and the results are pasted from the console. The separate-process part matters — see the newInstance() warning.

The minimum that works

Three steps: parse, make an options object, format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.pp.para.GFmtOpt;
import gudusoft.gsqlparser.pp.para.GFmtOptFactory;
import gudusoft.gsqlparser.pp.stmtformatter.FormatterFactory;

public class FormatDemo {
    public static void main(String[] args) {
        String sql = "select e.last_name as name, e.commission_pct comm, "
                   + "e.salary*12 \"Annual Salary\" from scott.employees as e "
                   + "where e.salary>1000 or 1=1 "
                   + "order by e.first_name,e.last_name;";

        // 1. Parse first — the formatter walks the AST, not the text
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = sql;
        if (parser.parse() != 0) {
            System.out.println(parser.getErrormessage());
            return;
        }

        // 2. Default style options
        GFmtOpt option = GFmtOptFactory.newInstance();

        // 3. Format
        System.out.println(FormatterFactory.pp(parser, option));
    }
}

Output:

1
2
3
4
5
6
7
8
SELECT   e.last_name      AS NAME,
         e.commission_pct comm,
         e.salary * 12    "Annual Salary"
FROM     scott.employees AS e
WHERE    e.salary > 1000
         OR 1 = 1
ORDER BY e.first_name,
         e.last_name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
using System;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.pp.para;
using gudusoft.gsqlparser.pp.stmtformatter;

class FormatDemo
{
    static void Main()
    {
        var sql = "select e.last_name as name, e.commission_pct comm, "
                + "e.salary*12 \"Annual Salary\" from scott.employees as e "
                + "where e.salary>1000 or 1=1 "
                + "order by e.first_name,e.last_name;";

        // 1. Parse first — the formatter walks the AST, not the text
        var parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = sql;
        if (parser.parse() != 0)
        {
            Console.WriteLine(parser.Errormessage);
            return;
        }

        // 2. Default style options
        GFmtOpt option = GFmtOptFactory.newInstance();

        // 3. Format
        Console.WriteLine(FormatterFactory.pp(parser, option));
    }
}

Output:

1
2
3
4
5
6
7
8
SELECT   e.last_name      AS NAME,
         e.commission_pct comm,
         e.salary * 12    "Annual Salary"
FROM     SCOTT.employees AS e
WHERE    e.salary > 1000
         OR 1 = 1
ORDER BY e.first_name,
         e.last_name;

Project setup, if you are starting from scratch:

1
2
3
4
5
mkdir formatdemo && cd formatdemo
dotnet new console
dotnet add package gudusoft.gsqlparser
# paste the code above into Program.cs
dotnet run

Note the default style: keywords are upper-cased and right-aligned in their own column, and values line up in a second column. Note too that name came out as NAME — the formatter treats some unquoted words as keywords. If that surprises you, set the casing options explicitly rather than relying on defaults.

The same input formats slightly differently in each edition

Compare the two tabs above: the schema is scott.employees on Java and SCOTT.employees on .NET, from identical input and identical default options. The editions classify some unquoted words differently (identifier vs non-reserved keyword), so default casing is not portable.

If you need byte-identical output across both editions, set caseIdentifier and caseKeywords explicitly instead of accepting the defaults.

The three moving parts

Type What it is Why you need it
TGSqlParser The parser. Reads SQL text, builds an AST. The formatter walks the AST, so the SQL must parse first.
GFmtOpt Style options. 70 public fields on Java, 69 on .NET. Everything you tune lives here.
FormatterFactory.pp(parser, option) The formatter. Returns the formatted SQL as a string.

There is no shorter API — you always need all three.

Why is the namespace pp?

The formatter began as a pretty printer, so the package/namespace and the method pp(...) both keep the abbreviation. Read it as "pretty-print".

pp(parser, null) throws — it is not a shortcut

Passing null for the options does not return the original SQL untouched. It throws: NullPointerException on Java, NullReferenceException on .NET. Always pass a real GFmtOpt.

A .NET-only trap: newInstance() is a shared singleton

This one will bite you in a real application, and it is the reason every recipe on this page was measured in a separate process.

Java .NET
GFmtOptFactory.newInstance() returns a fresh object yes no — the same instance every time
Mutating the result affects later callers no yes

On .NET, this sequence leaves the second format lower-cased too:

1
2
3
4
5
6
var a = GFmtOptFactory.newInstance();
a.caseKeywords = TCaseOption.CoLowercase;      // intended to be local to this call

var b = GFmtOptFactory.newInstance();          // NOT a fresh object
Console.WriteLine(ReferenceEquals(a, b));      // True
Console.WriteLine(b.caseKeywords);             // CoLowercase — leaked

So on .NET, treat the options object as global mutable state. Either build it once at startup and never change it again, or reset every field you touched before handing control back. A library that formats SQL on behalf of callers should not mutate the shared instance at all.

Reading from a file, writing the result back

Set sqlfilename instead of sqltext; the parser handles UTF-8 and UTF-16 with or without BOM, so do not pre-load the file yourself.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqlfilename = "/scripts/employees.sql";

if (parser.parse() != 0) {
    System.out.println(parser.getErrormessage());
    return;
}

String formatted = FormatterFactory.pp(parser, GFmtOptFactory.newInstance());
java.nio.file.Files.write(
    java.nio.file.Paths.get("employees-formatted.sql"),
    formatted.getBytes(java.nio.charset.StandardCharsets.UTF_8));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqlfilename = @"C:\scripts\employees.sql";

if (parser.parse() != 0)
{
    Console.WriteLine(parser.Errormessage);
    return;
}

string formatted = FormatterFactory.pp(parser, GFmtOptFactory.newInstance());
File.WriteAllText("employees-formatted.sql", formatted);

Write to a new path rather than over the input while you are still developing — a formatter bug that eats a script is much less painful when the original survives.

Picking the dialect

The formatter is only as good as the parse, and the parse depends on the vendor. If your script does not parse, that is almost always the wrong EDbVendor rather than a formatter problem: the error looks like state:NNNN(10102) near: SOMETOKEN(line,col).

Java exposes 45 vendor constants and .NET 23 — see Database Compatibility for the full picture, including the three .NET constants that throw. Note that dbvaccess is a distinct constant that routes to the T-SQL grammar, not an alias of dbvmssql.

Customisation recipes

Every knob is a field on GFmtOpt. The style enums live in their own namespace/package — gudusoft.gsqlparser.pp.para.styleenums — which is a fourth import beyond the three in the minimum example. Miss it and the recipes below do not compile.

1
2
3
import gudusoft.gsqlparser.pp.para.styleenums.TCaseOption;
import gudusoft.gsqlparser.pp.para.styleenums.TAlignStyle;
import gudusoft.gsqlparser.pp.para.styleenums.TLinefeedsCommaOption;

1
using gudusoft.gsqlparser.pp.para.styleenums;

Recipe 1 — lower-case keywords

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.caseKeywords = TCaseOption.CoLowercase;
String formatted = FormatterFactory.pp(parser, option);

For select e.last_name as name, e.commission_pct comm from scott.employees as e where e.salary>1000;:

1
2
3
4
select e.last_name      as name,
       e.commission_pct comm
from   scott.employees as e
where  e.salary > 1000;

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.caseKeywords = TCaseOption.CoLowercase;
string formatted = FormatterFactory.pp(parser, option);

Same input:

1
2
3
4
select e.last_name      as name,
       e.commission_pct comm
from   SCOTT.employees as e
where  e.salary > 1000;

The four casing values, identical in both editions:

TCaseOption value Effect
CoUppercase SELECT — the default for keywords
CoLowercase select
CoInitCap Select
CoNoChange leave as written

Apply them independently to five different token classes:

Field on GFmtOpt Affects
caseKeywords SELECT, FROM, WHERE, JOIN, …
caseIdentifier unquoted table and column names
caseQuotedIdentifier identifiers in "..." or [...]
caseFuncname function names such as count, sum
caseDatatype VARCHAR, INT, NUMBER, …

Recipe 2 — indentation

The option-setting code below is character-for-character the same in Java and C#:

1
2
3
4
GFmtOpt option = GFmtOptFactory.newInstance();
option.indentLen = 4;
option.useTab    = false;   // set true for real tabs
option.tabSize   = 4;       // visual tab width, only used when useTab = true

indentLen does not do what its name suggests

Setting indentLen = 4 produced output identical to the default in both editions for select a, b from t where a=1;:

1
2
3
4
SELECT a,
       b
FROM   t
WHERE  a = 1;

The default layout aligns continuation lines under a keyword column rather than indenting by a fixed number of spaces, so indentLen has no visible effect on ordinary SELECT statements. It applies to constructs that genuinely nest — BEGIN/END blocks, CASE expressions, sub-selects. Do not reach for it expecting to change SELECT list alignment; use the *Style fields for that.

Recipe 3 — comma at the start of the line

1
2
GFmtOpt option = GFmtOptFactory.newInstance();
option.selectColumnlistComma = TLinefeedsCommaOption.LfBeforeComma;

1
2
3
4
SELECT e.last_name       AS NAME
       ,e.commission_pct comm
       ,e.salary * 12    "Annual Salary"
FROM   scott.employees AS e;

1
2
GFmtOpt option = GFmtOptFactory.newInstance();
option.selectColumnlistComma = TLinefeedsCommaOption.LfBeforeComma;

1
2
3
4
SELECT e.last_name       AS NAME
       ,e.commission_pct comm
       ,e.salary * 12    "Annual Salary"
FROM   SCOTT.employees AS e;

TLinefeedsCommaOption has three values, not two:

Value Effect
LfAfterComma a, at end of line (default)
LfBeforeComma ,a at start of line, no space after the comma
LfbeforeCommaWithSpace , a at start of line, with a space

If you want the classic comma-first style with a space, LfbeforeCommaWithSpace is the one you want — LfBeforeComma packs the comma against the column name.

The same field exists as selectFromclauseComma, parametersComma, and defaultCommaOption (which catches everything else).

Recipe 4 — wrap the column list instead of stacking it

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.selectColumnlistStyle = TAlignStyle.AsWrapped;   // default is AsStacked
option.selectFromclauseStyle = TAlignStyle.AsWrapped;

1
2
3
SELECT e.last_name AS NAME, e.commission_pct comm, e.salary * 12 "Annual Salary"
FROM   scott.employees AS e
WHERE  e.salary > 1000;

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.selectColumnlistStyle = TAlignStyle.AsWrapped;   // default is AsStacked
option.selectFromclauseStyle = TAlignStyle.AsWrapped;

1
2
3
SELECT e.last_name AS NAME, e.commission_pct comm, e.salary * 12 "Annual Salary"
FROM   SCOTT.employees AS e
WHERE  e.salary > 1000;

TAlignStyle has exactly two values: AsStacked (one item per line, the default) and AsWrapped (fill the line, then break).

Recipe 5 — line up AND / OR under WHERE

Same code in both languages:

1
2
GFmtOpt option = GFmtOptFactory.newInstance();
option.andOrUnderWhere = true;

Identical in both editions:

1
2
3
4
5
SELECT 1
FROM   t
WHERE  e.salary > 1000
   AND e.commission_pct IS NOT NULL
   AND e.last_name LIKE 'A%';

Recipe 6 — tighter spacing inside expressions

Same code in both languages:

1
2
3
GFmtOpt option = GFmtOptFactory.newInstance();
option.wsPaddingOperatorArithmetic      = false;   // e.salary*12, not e.salary * 12
option.wsPaddingParenthesesInExpression = false;   // (1+2), not ( 1 + 2 )

Identical in both editions:

1
2
3
SELECT e.salary*12,
       (1+2)
FROM   t;

Recipe 7 — multi-statement scripts

Nothing special is required: every statement in sqltext is formatted, in order.

For select id from t1; select name from t2 where id = 1; update t1 set name = 'x' where id = 2;:

1
2
3
4
5
6
7
8
SELECT id
FROM   t1;
SELECT name
FROM   t2
WHERE  id = 1;
UPDATE t1
SET    name = 'x'
WHERE  id = 2;

Same input:

1
2
3
4
5
6
7
8
SELECT ID
FROM   t1;
SELECT NAME
FROM   t2
WHERE  ID = 1;
UPDATE t1
SET    NAME = 'x'
WHERE  ID = 2;

Two things to notice. There is no blank line between statements — if you want one, insert it yourself. And this is the clearest example of the casing divergence: .NET upper-cased id and name while Java left them alone, from the same input.

If one statement has a syntax error, parse() returns non-zero. Check getSyntaxErrors() / SyntaxErrors to find which one.

Recipe 8 — format a folder of .sql files

Construct a new parser per file. The parser holds the AST of the last script it parsed, and reusing one across files is more error-prone than the few microseconds a fresh instance costs.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
import java.nio.charset.StandardCharsets;
import java.nio.file.*;
import java.util.stream.Stream;

try (Stream<Path> paths = Files.walk(Paths.get("/scripts"))) {
    for (Path path : (Iterable<Path>) paths.filter(p -> p.toString().endsWith(".sql"))::iterator) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqlfilename = path.toString();
        if (parser.parse() != 0) {
            System.err.println(path + ": " + parser.getErrormessage());
            continue;
        }
        String formatted = FormatterFactory.pp(parser, GFmtOptFactory.newInstance());
        Files.write(path, formatted.getBytes(StandardCharsets.UTF_8));
        System.out.println("formatted " + path);
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
foreach (var path in Directory.EnumerateFiles(@"C:\scripts", "*.sql", SearchOption.AllDirectories))
{
    var parser = new TGSqlParser(EDbVendor.dbvoracle) { sqlfilename = path };
    if (parser.parse() != 0)
    {
        Console.Error.WriteLine($"{path}: {parser.Errormessage}");
        continue;
    }

    string formatted = FormatterFactory.pp(parser, GFmtOptFactory.newInstance());
    File.WriteAllText(path, formatted);
    Console.WriteLine($"formatted {path}");
}

On .NET, hoist the GFmtOpt out of the loop rather than calling newInstance() each time — it returns the same object anyway, so the call inside the loop is misleading.

Comments

Block comments (/* ... */) and line comments (-- ...) are preserved, in both editions. They do affect layout: a leading block comment pushes the whole statement across, because the formatter aligns to the comment.

For /* header */ select a, -- trailing / b from t where a=1;:

1
2
3
4
/* header */ SELECT a, -- trailing
                    b
             FROM   t
             WHERE  a = 1;

If comments vanish, you almost certainly modified the AST between parse() and pp(...), which can disconnect comment tokens from the nodes that owned them. Format first, then modify.

The full option surface

GFmtOpt carries 70 public fields (Java) / 69 (.NET). The ones above cover most needs; the rest group by prefix:

Category Field patterns Examples
Casing case* caseKeywords, caseIdentifier, caseFuncname
Column-list layout selectColumnlist* selectColumnlistStyle, selectColumnlistComma
FROM-clause layout selectFromclause* selectFromclauseJoinOnInNewline
INSERT layout insertColumnlist*, insertValuelist*
CREATE TABLE layout createtable*, beStyleCreatetable*
Indentation indentLen, useTab, tabSize
BEGIN/END blocks beStyleBlock*, beStyleFunctionBody*
Whitespace padding wsPadding* wsPaddingOperatorArithmetic
CTE / WITH cte* cteNewlineBeforeAs
CASE / WHEN caseWhenThenInSameLine, indentCase*
Procedure parameters parameters* parametersStyle, parametersComma
Empty lines *EmptyLines* via TEmptyLinesOption: EloMergeIntoOne, EloRemove, EloPreserve

Browse the API reference for the complete list; the field names are descriptive, so searching for the relevant word usually finds the knob.

Troubleshooting

parse() returned non-zero, so nothing was formatted

The formatter never runs if the parse fails. Check the vendor first — TOP 10, CONNECT BY and LIMIT 10,20 are all dialect-specific. Then read the error message, then getSyntaxErrors() / SyntaxErrors for line and column.

Output is empty

You called pp(...) before parse(). The formatter walks the AST that parse() builds; without it there is nothing to walk.

A NullPointerException / NullReferenceException from pp(...)

You passed null as the options argument. That is not a no-op shortcut — build a GFmtOpt with GFmtOptFactory.newInstance().

My style change affected other parts of the application (.NET)

GFmtOptFactory.newInstance() returns a shared singleton on .NET. See the warning above.

Output differs between the Java and .NET builds

Expected for default options — the editions upper-case unquoted identifiers differently. Set caseIdentifier and caseKeywords explicitly for portable output.

See also