I ran into an issue recently where I needed to fix two fields in a SQL statement.  Wasn’t a big deal except there were over 8,000 SQL statements requiring the change.  Would have loved to do a simple Replace String in Notepad++ but each value was unique.  The job that needed to be done was change a value from a string with single quotes around it to be a decimal number.  Simple enough, a value like ‘12.34’ needs to be come 12.34 for the SQL statement to be valid.

Anytime I working with random text files I tend to work in Notepad++.  I like the user experience it presents, it is light-weight, and fast.  Within the application you can perform the standard search and replace or you can use regular expressions to search the file.  I knew my search would have to involve regular expressions but what I wasn’t sure was whether I could replace the text with the cleaned version of itself.  To my surprise, and due to regex ignorance, this was 100% possible.  By taking advantage of a feature called Capture Groups a user can specify a search pattern as well as the specific part of the pattern you want to keep.  Then, for the replace value in the search and replace window you set the replacement value as \1 which represents the first, and in my case only, capture group.

What this all came down to was using the regular expression ‘(d\d\.d\d\)’ created a search term that looked for a two digit number with a decimal and two more digits after the decimal surrounded by single quotes.  The digits and decimal will be saved as part of capture group one.  Now I know there were probably much better ways to create a regex search term but this worked so I went with it.  Thank you to StackOverflow for getting me squared away with this problem.