Let’s take a tutorial like scenario to demonstrate power of Notepad++ as a developer productivity tool.
This approach can be used in other text editors like sublime, atom, etc that support regular expression (i.e regex) based find & replace.
Q. How will you extract rule_name values from a tabular data shown below and convert it to a comma separated values that can be used in an SQL WHERE clause.
Tabular data
|
1 2 3 4 5 6 7 8 |
id type rule_nmae bean_name ----------------------------------------------------- 633 ALL asx100_rule SECURITY_VALIDATION 632 ALL asx200_rule SECURITY_VALIDATION 634 ALL ETF_rule SECURITY_VALIDATION 635 ALL managed_fund_rule SECURITY_VALIDATION |
This data could come from an excel spread sheet, word document, or copied from a confluence or wiki page.
The SQL we need is
|
1 2 |
Select * from rules_table where rule_name in ('asx100_rule', 'asx200_rule', 'ETF_rule', 'managed_fund_rule'); |
A. Let’s see Notepad++ in action
Step 1: Copy the data to Notepad++ and delete the header row by highlighting it and pressing the delete button.
|
1 |
id type rule_nmae bean_name |
Step 2: You need to now remove all the columns except rule_name column. To do this place the cursor LHS of first 633 value and press
Step 3: Next step is to remove any leading or trailing spaces. Use regex based find and replace command. Pressing CTR+ F will bring the Find dialog . You can also select it from the “Search” menu at the top.
In the pop up find dialog, select the “replace” tab. Enter the find and replace value as shown below. Make sure the “Regular expression” option and “Wrap around” check box are ticked.
Don’t copy paste, but type.
Find What: [\s]+
Replace with: ,
Step 4: Remove the new line characters or carriage return by finding and replacing with the “Extended …” option turned on as shown below.
Don’t copy paste, but type.
Find What: \r
Replace with:
Replace new line with nothing.
Step 5: You need to put a single quote (‘) around the entries for the SQL query. Regex is agin back to the rescue.
Don’t copy paste, but type.
Find What: ([^,]*)(,?)
Replace with: ‘\1’\2
The parentheses ‘( )’ are used to capture the values. and \1 and \2 represent both the captured values. The ‘ is add before \1 and \2. Where \1 is the value like “asx100_rule” and \2 is “,”. The * means 0 or many, and ? means 0 or 1.
You can now take the single line text and put it in your where clause. This is very handy when you have to work with larger data.
This tutorial is good for learning regular expression as well.
