SQL in 60 Seconds is a series where I share SQL tips and tricks that you can learn and start using in less than a minute.
Watch this week's video on YouTube
Have you ever copied and pasted a query into SQL Server Management Studio and been annoyed that the list of column names in the SELECT statement were all on one line?
SELECT Col1, Col2, Col3, Col4, Col5,Col6,Col7, Col8, Col9, Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22,Col23,Col24,Col25,Col26,Col27,Col28,Col29,Col30 FROM dbo.MyTable
You can make the query easier to read by putting each column name onto its own line.
Simply open the Find and Replace window (CTRL + H) and type in ,(:Wh)* for the Find value and ,nt for the Replace value (in some versions of SSMS you may have better luck using ,(:Wh|t| )* in the Find field). Make sure "Use Regular Expressions" is checked and press Replace All:
The magic you just used is a Regular Expression, and Microsoft has its own flavor used in SSMS and Visual Studio. Basically, we found text that
- began with a comma (,)
- followed by any whitespace (:Wh) (line break, tab, space, etc…)
- (in newer versions of SSMS we add |t| to indicate or tab or space)
- and replaced it with a comma (,) and a new line (n) and tab (t).
Sure, this trick isn't going to give you the same output as if you used a proper SQL formatter, but this technique is free and built straight into SSMS.