VB - SQL 'Select' statement formatter/checker

[si_the_geek]

The attached module contains a function called FormatSelect, which formats an SQL Select statement string so that it is easier to read.
This should hopefully help you to make changes more easiy, or (if you have errors) enable you to see any mistakes much more quickly.

As a little bonus, the function will also notify you if there are obvious errors, such as the wrong number of quotes to mark the start/end of strings.

Example usage:

Dim sSQL as String
  sSQL = "SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3"
  sSQL = FormatSelect(sSQL)
  MsgBox sSQL

A couple of examples of the inputs/outputs of the function:
1) if the following Select statement is provided as input:

SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3

...it gets converted into this:

SELECT    Table1.Field1

FROM      Table1

WHERE     Field2 = 3

2) if this Select statement is provided as input:

SELECT Table1.Field1 AS Field1, Table1.Field2 AS Field2, CASE WHEN Table2.Field3 = 0 THEN 'NO' 
WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3, IIf(Field4=1,1,0)
From Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
Where Table1.Field5 Between '1/1/2005' AND '1/20/2005' AND Table1.Field6 Between '1/1/2005' AND '1/20/2005' 
OR Table1.Field7 = '1/20/2005' 
AND Table1.Field8 IN(SELECT Field1 FROM table4 WHERE field2 IN(SELECT Field1 WHERE a=b FROM table2))

...it gets converted into this:

SELECT    Table1.Field1 AS Field1,
          Table1.Field2 AS Field2,
          CASE WHEN Table2.Field3 = 0 THEN 'NO' WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3,
          IIf ( Field4 = 1, 1, 0 )

FROM      Table1
          LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
          LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1

WHERE     Table1.Field5 Between '1/1/2005' AND '1/20/2005'
          AND Table1.Field6 Between '1/1/2005' AND '1/20/2005'
          OR Table1.Field7 = '1/20/2005'
          AND Table1.Field8 IN (

                    SELECT    Field1
                    
                    FROM      table4
                    
                    WHERE     field2 IN (
                    
                                        SELECT    Field1
                                        
                                        WHERE     a = b
                                        
                                        FROM      table2
                    
                              )

...with the following warning message:

(in sub-query: 
  (in sub-query: 
    Incorrect section ordering: FROM should be before WHERE
    )
  )

Note that the blank lines are optional, and can be disabled like this:

sSQL = FormatSelect(sSQL, False)

The number of spaces to use as an indent (for 'normal' text, and for subqueries) can also be specified, using two further optional parameters. eg:

sSQL = FormatSelect(sSQL, , 4, 2)

An alternative version by CVMichael can be found here: VB - SQL Select Formatter.


Please let me know if there are any parts of SQL syntax which are not handled appropriately.