29 August 2012

Using Notepad++ to Search and Replace using Regular Expressions

Not long time ago I had to modify a 300 lines SQL Server stored procedure that uses columns containing spaces in the names into a version that does not contain spaces. For example columns like [Price Rate] should be converted to Price_Rate. Doing this manually is long and tedious task. Fortunately Notepad++ saved the day once again – use regular expressions.

To to search and replace the spaces for the column names with underscore open the Notepad++ Replace dialog (Ctrl+H) and type the following for the search pattern:

\[([0-9a-zA-Z]*) ([0-9a-zA-Z]*)\]

The above will match everything that starts with [ continues with combination of letters or numbers, has a space after that, has a combination of letter or number after the space and ends with ]

Type the following for the “Replace with” field:

\1_\2

This means that Notepad++ will replace the \1 with the match between the [ and the space from the column name. The \2 will be replaced with the match between the space and the ]. Here we are using one special feature – if part of regular expression is within a round brackets then this match can be tagged using \1, \2, \3, etc.

At the end select “Regular expression” for “Search Mode” and hit the “Replace All” button.

image

For more information about the special character of a regular expressions take a look here.

No comments: