sql nested select parsing

Question

Situation: I have several SSRS reports of which I need the sql-code to be documented. For this I need the code to be formatted in exactly the same way time and time again.

Problem: In some of my reports I have structures like

select outer
from (
select outin
from (
select inner
from (
select innerMax
from
)z
where
)x
where dateadd(d,12,getdate())
)y
where

And I want the result to be

select outer
from (
    select outin
    from (
        select inner
        from (
            select innerMax
            from
        )z
        where
    )x
    where dateadd(d,12,getdate())
)y
where

But I have problems with indenting the nested select's

I'd really appreciate it if you can provide me with examples. I've used splits, regex, substrings, ...

greetings

The chance that the queries will have sql-resemblance is quite small. Seeing on which database it is meant, there is no way that there will be string literals that look like sql. As for comments: I'm writing my code without comments, and formatting it so that it can be documented in an external file.(semi-bad practice, I know) — ShadowFlame
– ShadowFlame, Commented Sep 30, 2011 at 9:08
Still, using an existing tool that is made for this (an SQL parser) is IMO preferred. See my answer. — Bart Kiers
– Bart Kiers, Commented Sep 30, 2011 at 9:19

Bart Kiers · Accepted Answer · 2011-09-30 09:18:40Z

1

This is trickier than you might think: split, substring and regex will not be enough to reliably tackle this problem. Consider comments, or string literals inside your SQL code that might contain text that looks like SQL or contains parenthesis which will mess up your indentation levels.

A better way is to use an SQL parser. Here's a demo with python-sqlparse:

#!/usr/bin/env python
import sqlparse

sql = """
select outer
from (
select outin
from (
select inner
from (
select innerMax
from
)z
where
)x
where dateadd(d,12,getdate())
)y
where
"""

print sqlparse.format(sql, reindent=True)

which will print:

select outer
from
  (select outin
   from
     (select inner
      from
        (select innerMax
         from)z
      where)x
   where dateadd(d,12,getdate()))y
where

answered Sep 30, 2011 at 9:18

Bart Kiers

171k38 gold badges307 silver badges297 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql nested select parsing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related