Using Find and Replace to your advantage!

Learning how to use Find and Replace (CTRL+H) within SQL Server Management Studio can save you loads of time!

Firstly, I want to put a bit of a warning in about find and replace. It can save lots of time when repeating manual tasks of entering characters between words etc., but it can also cause huge data issues if you get the mix wrong. As long as you control what is going to be replaced and you consider the implications of your search criteria you’ll narrow the risk.

In this example, we have a list of Surnames 1-100 ordered by ‘PopularityIndex’ and we want to insert this data into one table using a union of the data. Currently the data is in the form [PopularityIndex],[Surname]. Firstly, we need to quote enclose the surname. Then we need to add another column to the end of the current data which is a Country_Key of 3 in this case. Then we need to UNION ALL the list of statements.

Of course there are other ways to import this data in, including BCP and SSIS. But if you master find and replace you’ll find it’s quicker to enter small amounts of data this way.

So our SQL Statement currently looks as follows:

INSERT INTO Surnames (PopularityIndex,Surname,Country_Key)
100,SMITH
99,BROWN
98,TAYLOR
97,JOHNSON
96,WALKER
95,WRIGHT
94,ROBINSON
93,THOMPSON

We know that there will always be a comma to the left of our surname and furthermore we know that none of our Surnames contain columns. So we can do a Find and Replace to find a , (comma) and Replace with ,’ (comma, single-quote). This will give us the following data:

INSERT INTO Surnames (PopularityIndex,Surname,Country_Key)
100,'SMITH
99,'BROWN
98,'TAYLOR
97,'JOHNSON
96,'WALKER
95,'WRIGHT
94,'ROBINSON
93,'THOMPSON

Now we need a single-quote at the end of the data… but there are no characters visible. If you were to look at the data in an application such as Notepad++ and show all characters, we can see that each line ends with a CR LF pattern:

This is a Carriage-Return, Line-Feed combination and is written into the file as a hidden character to show where each line ends. We can pattern match on these hidden characters in SSMS, but first it’s always wise to see which type of line endings are used. Some environments include different line endings to others. Generally Windows uses the CRLF combination.

Within SSMS you can pattern match on a variety of symbols and hidden characters – such as CR/LF and the TAB key. In the Find and Replace dialog in SSMS there is actually a handy link which shows us which characters to use to get the results we’re after. At the end of the ‘Find What:’ and ‘Replace With:’ fields there’s a play button which drops down a menu. At the bottom of this menu is a link to the full reference document:

So now you know how to find that document in future to help you with your intelligent Find and Replaces, let’s go back to our query. We know now we need to replace our CRLF’s with a single quote – not forgetting to put our CRLF back at the end!

The backslash is used as an escape character for a few standard hidden characters, so for example \n refers to a ‘new line’, and \t refers to a ‘TAB’ key. So let’s replace the term \n with ‘\n:

This will put a single-quote at the end of each line for us – that’s saved a lot of key presses right?!

Now let’s add our last column in using the same technique, find \n and replace with ,3\n This will put a ,3 at the end of every row. That’s our last column sorted. Now we’ll need a SELECT at the start of every row, so replace \n with \nSELECT (remember to include a space at the end of ‘SELECT ‘). So you know what’s next now – let’s replace our new lines in order to insert our ‘UNION ALL’ statement:

You’ll notice the first line ‘INSERT INTO…’ also includes the column, so delete the column from there and the UNION ALL which is underneath. You’ll also need to remove any extra lines, in my case there was an extra SELECT at the end of the file. Sorted – you can run your INSERT statement and send those rows in:

INSERT INTO Surnames (PopularityIndex,Surname,Country_Key)
SELECT 100,'SMITH',3
UNION ALL
SELECT 99,'BROWN',3
UNION ALL
SELECT 98,'TAYLOR',3
UNION ALL
SELECT 97,'JOHNSON',3
UNION ALL
SELECT 96,'WALKER',3
UNION ALL
SELECT 95,'WRIGHT',3
UNION ALL
SELECT 94,'ROBINSON',3
UNION ALL
SELECT 93,'THOMPSON',3

Now, what if we went wrong? Run the \nUNION ALL\n statement again. This will create 3x UNION ALLs between every SELECT. Now if we wanted to remove those 3x UNION ALLs and replace them with just one, we’d run the following:

Back to normal!

I hope this blog encourages you to use the Find and Replace tool more intelligently and saves your fingers from unnecessary key strokes! Remember to always check your data and that you can use undo at any point to roll back a find and replace.