Visual Studio/SQL Server Management Studio Find and Replace with Regular Expressions

I needed to format a bunch of strings in SQL Management Studio (this works for VS also) and finally took the time to figure out Find/Replace with Regular Expressions.

The first thing to note is that VS/SSMS use different regex codes (don’t know why but I’m sure they have their reasons). You can find the list of codes here.

I have list of 3 strings containing 10 digits followed by a space and then 3 letters. I wanted to replace the digits with the word Descriptor.

Here’s my data:

1234567870 ABC
1234567880 DEF
1234567890 GHI

Here’s my find expression:

[0-9]^10 {[A-Z]^3}

Here’s my replace expression:

Descriptor: \1

Here’s my result:

Descriptor: ABC
Descriptor: DEF
Descriptor: GHI

The key is the {} brackets and \1. The {} brackets tell the engine I want to keep whatever matches the expression. This is called a Tagged Expression. The \1 says place the first tagged expression here. I can create multiple tagged expressions and place by using \1, \2, etc. I can even change the order in the result because they are numbered based on their order in the original expression. You can use newlines (\n) so that you can break lines apart).

This is great for creating some testing stuff or moving non-standard data.

Sweet!

Subscribe in a reader