Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 761
atricapilla Avatar asked Apr 30 '10 05:04

atricapilla


People also ask

How do you delete blank rows in SQL?

Use the delete command to delete blank rows in MySQL. delete from yourTableName where yourColumnName=' ' OR yourColumnName IS NULL; The above syntax will delete blank rows as well as NULL row. Insert some records in the table using insert command.

How can remove blank space from string in SQL Server?

SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.

How do I select multiple lines in SSMS?

To leverage this feature, you need to hold down the ALT key, then left click on your mouse to drag the cursor over the text you want to select and type/paste the text you want to insert into multiple lines.


1 Answers

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

like image 149
Wenlong Guo Avatar answered Oct 26 '22 13:10

Wenlong Guo