How can I remove empty lines in SQL Server Management Studio (SSMS)?

Question

I have many .sql files with lots of empty lines, for example,

WITH

  cteTotalSales (SalesPersonID, NetSales)

  AS

  (

    SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)

    FROM Sales.SalesOrderHeader

    WHERE SalesPersonID IS NOT NULL

    GROUP BY SalesPersonID

  )

SELECT

  sp.FirstName + ' ' + sp.LastName AS FullName,

  sp.City + ', ' + StateProvinceName AS Location,

  ts.NetSales

FROM Sales.vSalesPerson AS sp

  INNER JOIN cteTotalSales AS ts

    ON sp.BusinessEntityID = ts.SalesPersonID

ORDER BY ts.NetSales DESC

Is there a way to remove these empty lines in SQL Server Management Studio (SSMS)?

This is what I would like to have:

WITH
  cteTotalSales (SalesPersonID, NetSales)
  AS
  (
    SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
  )
SELECT
  sp.FirstName + ' ' + sp.LastName AS FullName,
  sp.City + ', ' + StateProvinceName AS Location,
  ts.NetSales
FROM Sales.vSalesPerson AS sp
  INNER JOIN cteTotalSales AS ts
    ON sp.BusinessEntityID = ts.SalesPersonID
ORDER BY ts.NetSales DESC

Ordway

Answer 1

It is not built in. The find and replace can be used with regex's and someone crafty may have a solution for that.

Caines

Thanks, I worked out this by myself like this: Replace using regular expression, and find line breaks at the beginnign of line: ^\n. Repalce with nothing. – Ordway

@all, check the next answer for the steps – Farrier

Answer 2

You can do it using the regular expression in SSMS:

1. Ctrl-H to bring up the Find And Replace window
2. Select USE -> Regular Expressions
3. Put ^\n in the Find What
4. Keep Replace With empty
5. Click Replace (All)

Good luck

Cable

This should be the answer, not sure why the other one is. – Instructor

@NoSaidTheCompiler because it is the correct answer, was given 6 minutes after the question was asked and allowed the asker to find the specific solution needed. As opposed to this one which propose the same solution, copies information that was already in a comment by the asker on the accepted answer and was given 4 months after the answer was marked as accepted. It may be a better answer since it is clearer but we can see this is reflected in the votes. All is as it should be, the system works! :-) – Fennie

@Fennie - totally makes sense. Thanks for taking time to provide the explanation. – Instructor

Answer 3

I have gone through the below steps, and it worked for me.

1. Menu Tools → Customize → Commands → Add command → Edit → Delete blank line → OK.
2. The Delete Blank Lines menu appears in beside the File menu.
3. Open/Select Query - Click "Delete Blank Lines".
4. Enjoy.

Loveinidleness

Thanks - this was the only thing that worked for me Regex couldnt find \^n – Triumphant

Strange, I also had to resort to this as RegEx didn't work. Would be interesting if someone could shed some light on why. – Perorate

This is the correct answer - why muck about with RegEx when there is a built in way to do it? – Slaby

Answer 4

You can use: Find and replace

• Find what: \n\n
• Replace with: \n
• use: 'regular expressions'

it is described here:

http://my.safaribooksonline.com/book/databases/microsoft-sql-server/9781617290473/using-regular-expressions-in-ssms/ch21lev1sec1#X2ludGVybmFsX0h0bWxWaWV3P3htbGlkPTk3ODE2MTcyOTA0NzMlMkZjaDIxbGV2MXNlYzImcXVlcnk9

Niddering

It seems to work here Find what: \n\n* and Replace with: \n – Capper

Answer 5

Menu Tools → Customize → Commands → Add command → Edit → Delete blank line → OK.

Mounts

Answer 6

Redgate Sql Toolbelt is good for this. This package has Sql Prompt and Sql Refactor which allows easy formatting of your query (even from very very bad formatting). It will allow you to cut on spaces, move stuff around according to your needs.

Code completion As you type SQL Prompt provides unobtrusive support, suggesting appropriate keywords, tables, views, and other database objects. It even suggests complete join conditions based on foreign key constrains or matching column names. Where it makes sense SQL Prompt will complete entire statements for you, such as INSERT or ALTER VIEW.

SQL reformatting (Pro edition only) The Format SQL command reformats any SQL to match your chosen coding style. Clear and accurate formatting make it much easier to understand complex SQL, and helps maintain a consistent style across your entire team.

It's not free but definitely worth a try if you have budget for it.

Anachronistic

Is there a corresponding "coding standard" for SQL? – Bjork

Answer 7

Those of you finding that a regex find-and-replace of ^\n with blank doesn't work, I had the same problem because my file contained CR LF rather than just LF. In this case using ^\r\n instead worked for me.

Handsomely

Answer 8

For all those (like me) who have issues with ^\n not finding any regex matches, I was able to remove the empty lines in my SSMS editor using;

\r\s+          in the Find box

\r             in the Replace box

Afc

Answer 9

Use Find and Replace with fine '^\n' and in replace leave it blank.

Additionally, check Use regular expression in the Find option.

Deegan

What do you mean by "with fine"? (Please respond by editing your answer (without "Edit:", "Update:", or similar), not here in comments). – Bjork