Formatting SQL in Notepad++

Suppose you have the following text in your editor:

select FIELD1, FIELD2, FIELD3 from TABLE1;

And you want this formatted to look like this:

select 
    FIELD1,
    FIELD2,
    FIELD3
from TABLE1;

How would you do it?

Well here's an example using search-replace and regular expressions in NotePad++. The reason I'm using Notepad++ is that it seems to be better with regular expressions than most other editors.

There are three steps involved:

  • Replace the select and white space with a select, new line and tab.
  • Replace the commas and white space with a comma, new line and tab.
  • Replace the white space and from with a new line

Menus: Search > Replace (or CTRL + H)
Set the Search Mode to Regular expression (see screenshots below)

Replace the select and white space with select, new line and tab:

Find what: select (don't forget the whitespace)
Replace with: select \n\t (newline & tab)

replace_step1.png

This gives you:

select 
    FIELD1, FIELD2, FIELD3 from TABLE1;

Replace the commas and white space with a comma, new line and tab:

Find what: , (don't forget the whitespace)
Replace with: ,\n\t

replace_step2.png

This gives you:

select 
    FIELD1,
    FIELD2,
    FIELD3 from TABLE1;

Replace the white space and from with a new line and from:

Find what: from (don't forget the whitespace)
Replace with: \nfrom

replace_step3.png

This gives you the desired result:

select 
    FIELD1,
    FIELD2,
    FIELD3
from TABLE1;

Alternatively, check out the article on auto generating a select statement.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License