How to format SQL inserts to align the comma separators?

I have tried a silly way to solve your problem. First I am assuming that all the rows have three columns. Insert the scripts in a table and use below query to have them all formatted as you desire. ( I have created a table named scripts and inserted the scripts in script column)

with cte as (
select *,charindex (',',script) FirstSplit,
charindex (',',script,charindex (',',script)+1) SecondSplit,
charindex ('),',script,charindex (',',script,charindex (',',script)+1)+1) ThirdSplit
from scripts
),
cte2 as (select * ,max(firstsplit)over(order by (select null)) MaxFirstSplit,max(SecondSplit)over(order by (select null)) MaxSecondSplit ,max(ThirdSplit)over(order by (select null))MaxThirdSplit  from cte) 

select (substring(script,1,firstsplit-1)+space(maxfirstsplit-firstsplit)+',') +
(substring(script,FirstSplit+1,SecondSplit-FirstSplit-1)+space(MaxSecondSplit-SecondSplit-(MaxFirstSplit-FirstSplit))+',' )+
(substring(script,SecondSplit+1,ThirdSplit-SecondSplit-1)+space(MaxThirdSplit-ThirdSplit-(MaxSecondSplit-SecondSplit))+'),') NewScript
from cte2

Input:

 script
    (123, 'File_X', 'ShortString'),
    (124, 'File_XYZ', 'LoooonnnngString'),
    (124, 'File_XYZ', 'LgString'),
    (124, 'F_XYZ', 'Lgring'),
    (1, 'F_XYZ', 'Lgring'),

Output:

NewScript
(123, 'File_X'  , 'ShortString'     ),
(124, 'File_XYZ', 'LoooonnnngString'),
(124, 'File_XYZ', 'LgString'        ),
(124, 'F_XYZ'   , 'Lgring'          ),
(1  , 'F_XYZ'   , 'Lgring'          ),

UPDATE

Turns out there is a much easier way of doing this in Notepad++:

  1. Install the TextFX plugin if you don't already have it (by going to Plugins -> Plugins Admin... and searching for and selecting TextFX, then restarting when prompted).
  2. Press CTRL+A to select all your text
  3. Go to TextFX -> TextFX Edit -> Line up multiple lines by (,)
  4. Job done!

Old answer for posterity...

I don't know a way of automating this but it can be done fairly simply in Notepad++ as follows:

  1. Press CTRL+H to open the Replace window.
  2. Select the "Regular expression" radio button under "Search Mode".
  3. In "Find what" enter \h+ and in "Replace with" enter \t (i.e. to replace each block of whitespace with a single tab).
  4. Click on "Replace All".
  5. Now adjust the tab settings via Settings -> Preferences -> Language and play around with the "Tab size" number until all items are aligned (choosing as low a number as possible - for your example I used 6).
  6. Now go to Edit -> Blank Operations -> TAB to Space (to convert the tabs back into spaces).
  7. You should now be quite close to what you've asked for but there is likely to be more whitespace than needed in some places. If you care about this, an additional manual is needed... In the top line, go to the position of where you'd like to delete whitespace from and hold down ALT+SHIFT and then hold the down arrow key until you're at the very last line. You will then be in "block mode" and can use the backspace key to delete spaces from all the lines. When done, press any cursor key to exit from block mode and repeat as needed.

I wrote it as a comment, but as suggested by @zimek-atomek I'll make it as an answer to be clearer.

To align all the columns in the file, you can use the plugin in for Visual Studio Code called Rainbow CSV (https://marketplace.visualstudio.com/items?itemName=mechatroner.rainbow-csv)

Since the file you are going to align contains commas (but it also support other delimiters), you can use the align columns feature mentioned in the list at the beginning of the page.

Tags:

Sql

Formatting

Recent Posts