Search and replace text by regular expression

Introduction

Surely any, even the most non-advanced PC user, is able to search and replace lines to text. In the general case, it is necessary to indicate the line to be replaced and the line to be replaced. This is enough to achieve the simplest goals. But sometimes you want more - not just replace, but, for example, change the structure of the lines.

More difficult goal

Consider the problem that I encountered in the process of my work. It seems to me that this example sufficiently reveals the essence of the current topic.

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



As a source, I was presented 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 enum (enum), right? Add a few entries to such a table is not difficult. And if there are 1000+ such records? Agree, manually adding each there is no desire at all. In addition, the table had to be updated both locally and on the customer’s test server (and in the future on the live server too). Therefore, it is advisable to write an SQL script. And you can make a text editor write it for me!

goal

So, the task is to convert each line of the source text into an SQL query that would perform INSERT writes to the desired table. For example:

FirstBackordered = 38,

you 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 regular expression search and replace, such as Notepad ++, Visual Studio, SQL Management Studio, etc. Please note that the format of the regular expression may vary in different programs. This article discusses the search and replacement of text in Microsoft software products.

Decision

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

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

^ - Start of 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 +, =, ~, |, <and>;
: d - Decimal digit. Matches the expression ([0-9]);
$ - End of line. Matches an entry only if it is at the end of the line of text in which the search is performed.

In Find and Replace modebraces ({}) are used for tagged expressions. This regular expression will find each line in the source text file. With tags we made a name (e.g. FirstBackordered) and a number (38). Expressions in curly brackets can be used in the “Replace with” field - \ 1, \ 2, \ 3, etc. We’ll write an expression to 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 regular expression.

Conclusion

As a result, by clicking on the “Replace All” button, we save a lot of time and increase our sense of personal greatness: D



Thank you for your attention. Hope to help someone avoid boring pipeline work.