Select Distinct Logo Clear Background

Business Analytics Blog

Remove Carriage Returns in SQL Server SSMS

How to remove carriage returns in SSMS

How to Remove Carriage Returns in SQL Server Management Studio?

It is a fairly common task, a lot of people manually select and remove carriage returns and new lines, which can take time and is error prone

But you can use find and replace functionality built into SSMS to make the task more efficient

This step by step guide shows you how to use the standard find and replace with a few settings you may not know about

Typically SQL code often starts out looking like this, lets say you have run a Select Top 1000 * from ….

Sample SQL script

Start by selecting the rows you need to remove the spacing from

Selected code highlighted in SQL

Now, press Ctrl+H to bring up the find and replace dialogue box

using find and replace in SSMS

Next, to remove the new line (invisible) character, we need to type ‘\n’ into the find box

Ensure the second box (Replace) is empty, then click replace all

using find and replace to replace new lines character

This has replaced the new line character, but there is also a carriage return character

Repeat the above steps to replace the ‘\r’ (carriage return) with a blank

You can also do the same for the extra spaces that may be left behind too

The end result

Your finished code should look like this

compact, clean SQL code

You can watch the video on our You Tube Channel

With these find a replace shortcuts you can easily make your SQL code much more compact to read, without the need to manually remove the spaces and carriage returns in the SQL Server Management Studio SQL editor

You may be interested in this post
How to show line numbers in SQL Server (SSMS)

Subscribe to our channel to see more Power BI Timesavers

Select Distinct YouTube Channel

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert