[SQL Server] Replacing text using RegEx(Regular-Expression) in SSMS

[SQL Server] Replacing text using RegEx(Regular-Expression) in SSMS

There is a functionality to find and replace the text using the built-in Regular-Expression.

Here are some examples.

1. Replace new-line to ","
This can be useful when you want to get column names to CSV easily.

Get columns information by using sp_help command or press ALT+F1.

Copy and paste column list only

Select Menu > Edit > Find and replace > Quick Replace or Press Ctrl+H

From: "\r\n", To: "," and Select "Use Regular Expressions" / Alt+E and Replace

*Note 01: Replace window may look different depending on SQL Server Management Studio Version
*Note 02: Depending on the system and the configuration, line-feed can be different. "\r\n", "\r" or "\n" (LF + CR, CR or LF only)
*Note 03: You can use "\t" for tab.


Now they had been replaced.

2. Replace multiple words to one word
This can be useful when you want you want to replace multiple words but actually they have only one meaning.

Let's change "location" and "address" to "addr".

Here's the example words

Open Replace - Ctrl+H

From: "location|address", To: "addr" and Select "Use Regular Expressions" and Replace

Now you can see that they had been replaced.

3. Use wildcard to replace numbers to blank
When you want to remove numbers from the string, you can do that as below.

Here's the example words

Open Replace - Ctrl+H

From: "[0-9]", To: ""(blank) and Select "Use Regular Expressions" and Replace

Now you can see that numbers had been disappeared.