Formatting SQL with Notepad++

By: Vlamis Staff
November 10, 2016
Related Topics: , ,

If you have ever had to format SQL in various editors of various flavors you most likely have experienced severe frustration at the lack of ability to apply personal formatting needs to SQL.

I experienced just that issue not long ago trying to format TSQL to line break every 3rd comma. Thankfully with the help of every programmers best friend (Notepad++) and most programmers source of utter befuddlement (Regular Expressions) I was able to solve the issue easily.

Imagine you have a SQL statement that looks like this –

select [Field1],[Field2],[Field3],[Field4],[Field5],[Field6]
    from [Some_Table]

but it’s more like 65 fields with a preceding insert statement and proceeding group by clause and the editor you are using doesn’t support formatting in any useful way. You could manually format it and then when you get another statement do it again and then jam a pen in your eyeball or you could use the dark and mysterious art of regular expressions.

So to insert a line break every third comma you just need to put your SQL into Notepad++, use TextFX Unwrap text option to get it all on a single line, sacrifice a chicken while dancing backwards around 13 tuples chanting “Chamberlin Boyce Codd” and enter the following in the Find / Replace dialog in Notepad++ –

The ((.?,.?)(.?,.?)[,]) means find and select everything between the starting point and the 3rd comma and the \1\r\n means replace it with itself and a newline.

Why does it work? Magic of course. Hopefully, this helps some poor soul.

If you want to learn more I borrowed my logic from –

Notepad++: A guide to using regular expressions and extended search mode

Replace every nth instance of a character with a new line in Notepad++

Share on linkedin
LinkedIn
Share on twitter
Twitter
Share on facebook
Facebook
Share on reddit
Reddit

Related Posts:

Small Is Beautiful

In the construction world, bigger is often better. For large construction projects you need large-scale machinery and spend much of your time developing a blueprint

Read More »

Let’s discuss your options

Contact us to discuss next steps.