Find and replace text by regular expression

Introduction

Surely anyone, even the most non-advanced PC user, can search for and replace strings to text. In general, you must specify the string to be replaced, and the string to which the replacement will be made. This is enough to achieve the most simple goals. But sometimes you want more - not just replace, but, for example, change the structure of strings.

More difficult goal

Consider the task that I faced in my work. I think this example adequately reveals the essence of the current topic.

So, in the process of developing software, I had to add a large number of records to the database table. The table structure is as follows:


')
As the source data I was provided with a text file of the form:

FirstBackordered = 38,
SecondBackordered = 39,
ThirdBackordered = 40,
FirstCreditCardDeclined = 41,
SecondCreditCardDeclined = 42,
ThirdCreditCardDeclined = 43,
FirstPayPalDeclined = 44,
SecondPayPalDeclined = 45,
ThirdPayPalDeclined = 46,
FirstDeclinedCreditCardBackordered = 47,
SecondDeclinedCreditCardBackordered = 48,
ThirdDeclinedCreditCardBackordered = 49,
FirstDeclinedPayPalBackordered = 50,
SecondDeclinedPayPalBackordered = 51,
ThirdDeclinedPayPalBackordered = 52

It looks like an excerpt from the listing (enum), right? Add a few entries in such a table is not difficult. And if such records 1000+? Agree to manually add each absolutely no desire. In addition, it was necessary to update the table locally and on the customer’s test server (and also on the live server in the future). Therefore, it is advisable to write a SQL script. And you can get a text editor to write it for me!

purpose

So, the task is to convert each line of the source code into a SQL query that would perform INSERT records in the desired table. For example:

FirstBackordered = 38,

need to convert to:

 insert into [LPEmail].[dbo].[EMAIL_TYPE] values ( 38, 'FirstBackordered', 'D:\Websites\LeisurePro\XslFiles\LP_BO\FirstBackordered.xslt', 'D:\Websites\LeisurePro\XslFiles\LP_BO\FirstBackordered.xslt', 1, 1 ); 

You can do this in any text editor that supports searching and replacing by a regular expression, such as Notepad ++, Visual Studio, SQL Management Studio, etc. Note that the format of a regular expression can vary in different programs. This article is about finding and replacing text in Microsoft software products.

Decision

So, we write a regular expression to search for strings in the source text file:

^{:w}:Zs:Sm:Zs{:d:d},*$

^ - Beginning of the line. Matches an entry only if it is at the beginning of a line of text in which the search is performed;
: w - Any string of letters. Matches the expression ([a-zA-Z] +);
: Zs - Matches a space;
: Sm - Mathematical symbol. Matches with +, =, ~, |, <and>;
: d - Decimal digit. Matches the expression ([0-9]);
$ - End of line. Matches an entry only if it is at the end of a line of text in which to search.

In Find and replace mode, curly braces ({}) are used for tagged expressions. According to this regular expression, each line in the source text file will be found. Tags we made the name (for example, FirstBackordered) and number (38). The expressions enclosed in braces can be used in the following in the field “Replace with” - \ 1, \ 2, \ 3, etc. Let's write the expression with which we want to replace the found lines:

insert into [LPEmail].[dbo].[EMAIL_TYPE]\nvalues (\n\t\2, '\1', 'D:\\Websites\\LeisurePro\\XslFiles\\LP_BO\\\1.xslt', 'D:\\Websites\\LeisurePro\\XslFiles\\LP_BO\\\1.xslt', 0, 1\n);

As you can see, the tags \ 1 and \ 2 are used, and more than once. These tags are replaced with real substrings found in the string by a regular expression.

Conclusion

As a result, by pressing the “Replace All” button we save a lot of time and increase our own greatness: D



Thanks for attention. I hope to help someone avoid boring conveyor work.