Regular Expression Search & Replace in Visual Studio Code

Kind of a diversion into a Visual Studio Code/Azure Data Studio tip here (this works in both, as well as SQL Server Management Studio). I’m not a regular expression guru - far from it. I use them occasionally, but usually find myself fumbling around for a bit trying to figure out just the right expression to do what I need.

I’ve known for a while that VSCode/ADS had regular expression matching built into its find/replace feature, but did you know you can also replace with it? It can help remove the tedium of doing a large amount of text processing.

The Challenge

I was faced with needing to remove the quotes around the numbers in this piece of code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

create table #MyTable (
    id int identity(1,1) primary key clustered not null
    ,MenuItem nvarchar(20) not null
    ,SortOrder tinyint not null default 99
);

insert into #MyTable (MenuItem,SortOrder) values
('First Item', '1')
,('Second Item','2')
,('Third) Item','10');

Find

Finding the numbers wrapped in single quotes with a regex is pretty easy - the expression is '\d+', which matches one or more “digit” characters surrounded by single quotes. Plug that into the Find window in the editor and make sure “Use Regular Expression” is selected. The editor highlights the matching strings.

But once you match, how do you replace automatically? Enter Capture Groups. By surrounding a portion of the expression that we’re matching with parentheses, the regex engine will “remember” what it found and allow us to use it later. You can capture multiple groups, and they’re referred to sequentially - the first one is $1, the second is $2, and so on.

Replace

Now we’re able to locate the text that needs to be modified and update each occurrence of it appropriately. The “Find” portion becomes '(\d+)', and the “Replace” is just a reference to the capture group, $1. Click that Replace All button et voilà, you’ve removed those single quotes, but only where appropriate.

I’ve known capture groups were a thing in regex since I first encountered them, but never thought to try applying them in this way. What a terrific surprise when I tried to use one here and it just worked!

Second Example

That’s a pretty basic example, how about something a little more practical. Let’s say I receive a query from someone accustomed to writing code for MySQL and they’ve used backticks (`) to quote the identifiers. But I want to change these to the square brackets ([]) that tend to be the convention in SQL Server. I can’t just replace the individual characters because the “find” character is the same on each end of the string I need to wrap while the replacement differs. I need to use a capture group again around the “word character” match, with the group’s reference wrapped in the replacement characters.

Wrap Up

I’ve been burning a lot of my text processing/formatting/clean-up time doing hacky workarounds and multi-step changes the past few years. Now that I know I can use capture groups in Visual Studio Code & Azure Data Studio, I’m rethinking my approach. I expect this will be a huge time- and trouble-saver and push me to get more comfortable with using regex. Just be careful with them - you don’t want to use regex to solve one problem and instead end up with two.