I was recently asked to copy some data in a SQL Server database but to exclude records which had the words 'To Delete' in their names. This is a simple enough task - throughout the SQL I wrote, I added to all the WHERE clauses this line:

AND ISNULL([Name], '') NOT LIKE '%To Delete%'

Predictably enough, the person who had written 'To Delete' in the names of various entities had not been consistent. Once the script was tested, the requirement was changed to also exclude records whose names include 'To be deleted'.

It is easy enough to copy & paste the above line, and change the words 'To Delete' into 'To be deleted', but this was in a very lengthy script and I wanted to replace all occurrences reliably. This is when I reached for regex find and replace.

First, we need to find all uses of that line. I want to preserve indentation, so I've captured the spaces at the start of the line in a capture group ( *). I've also captured the column identifier using (.*) - in the above example this is [Name] but in other parts of the script there could be variations such as x.[Name] or x.[Description].

( *)AND ISNULL\((.*), ''\) NOT LIKE '%To Delete%'

We can then replace this with the following text, making use of the two escape groups ($1 and $2).

$1AND ISNULL\($2, ''\) NOT LIKE '%To Delete%'
$1AND ISNULL\($2, ''\) NOT LIKE '%To be deleted%'