Coding a VBA SQL Formatter

[c19h28O2]

Hi,

Wondering if any of you guys could help me with writing a SQL Formatter.

The main problem I'm having is not so much the code but the thought process behind it.

I'm stuck on how and where to even begin.

The first thing I want to try is to work with is a best case scenario

So for example someone inputs a line of SQL

SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1

Which in turn in formatted as

SELECT.FIELD1,
...........FIELD2,
...........FIELD3
FROM...TABLE1

(dots are to display formatting and not part of the actual output)

Any help you could give me would be appreciated.


This will work via a form with a button and a textbox (for now)

Thanks

[Bob Phillips]

What is the purpose of this tool? I cannot see any advantage to having it formatted one way or another.

[JKwan]

Try out this link

http://vbrad.com/article.aspx?id=57

[c19h28O2]

What is the purpose of this tool? I cannot see any advantage to having it formatted one way or another.

I could probably give you a few reasons why, however it really depends what you like, if you can read code without any structure then I'm envious, I on the other-hand find it difficult to read streams of SQL without any formatting.

Any other developers like to give a reason as to why formatting any code is important?

Cheers

[Bob Phillips]

I agree formatting code is important, which is why I format it when I WRITE it. I don't just code it freehand and then go and run some formatter on it. And I rarely if ever find SQL that I can just pick up and run, especially complex SQL, so I either recut it or build it from scratch after understanding the principles. SQL has never seemed like VB to me (and I don't like using other people's VB much either).

Even the SQL I see is usually formatted.

But if you have some SQL that you can use wholesale, I don't see much point in a formatter, especially as it is like chasing a dog's tail, you never catch up.

[c19h28O2]

Totally - I think all code should be formatted as and when written rather than after... which is why I guess I want to write this tool, I spend most of my time formatting nested CASE statements, SELECTs, sub-queries etc.

Most of the SQL is of a fairly decent standard so a lot of the time it doesn't need re-writing but does need an element of readability added to it. Also not to mention that we are now required to adhere to a certain format (management has become aware how bad this problem is!) and changing a few thousand scripts by hand isn't going to cut it....

I guess your like me xld in that I tend to compare other peoples work with my own wether it be the format, style, how elegant it is. Suppose I'm pretty anal like that...

Obviously this tool is a cure rather than a prevention, but I think until our contractors wake up and smell the formatting it will be a massive benefit to us, not to mention saving us a whole bunch of hours doing monkey work!

[Ben S.]

Does anyone else have a solution? I'm making a utility to convert Access Jet SQL to T-SQL. My attempt at formatting and highlighting has some quirks and I'd like to see if something better exists.

Convert_Access_to_TSQL.jpg

[SamT]

Write your utility in PERL

If you must use VBA, set a reference to the VBIDE dll and make creative user of "Replace(String...)"

[Bob Phillips]

Why re-invent the wheel?

https://poorsql.com/