Notepad++ productivity tips with SQL trigger code example

One of the traits of good developers is that being lazy and this laziness leads to finding productive ways to perform a task. Recently, I had to create a number of triggers to a few existing tables. It is quite a repetitive task, hence I decided to use notepad++ regex feature with find/replace to make a bit more interesting and productive. Here is what I had to do

From existing DDL

CREATE TABLE bus_rule_audit(
    id                           NUMBER(30)     NOT NULL,
    code                         VARCHAR2(50)   NOT NULL,
    rule_entry_id                NUMBER(30)     NOT NULL,
    name                         VARCHAR2(100)  NOT NULL,
    effective                    date           NULL,
    expires                      date           NULL,
    created_by                   VARCHAR2(30)   NOT NULL,
    created_date                 TIMESTAMP      NOT NULL,
    updated_by                   VARCHAR2(30)   NOT NULL,
    updated_date                 TIMESTAMP      NOT NULL,
);

To, following INSERT statement in the trigger

CREATE OR REPLACE TRIGGER bus_rule_trig

AFTER INSERT ON bus_rule

FOR EACH ROW
 
  BEGIN

      INSERT INTO bus_rule_audit (ID,CODE,RULE_ENTRY_ID,NAME,EFFECTIVE,EXPIRES,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,CRUD_OPERATION) 
        VALUES (:NEW.ID, :NEW.CODE,:NEW.RULE_ENTRY_ID,:NEW.NAME,:NEW.EFFECTIVE,:NEW.EXPIRES,:NEW.CREATED_BY,:NEW.CREATED_DATE,:NEW.UPDATED_BY,:NEW.UPDATED_DATE,  crud_operation);

       
  END;

Here are the steps to perform the transformation with minimal or no typing involved.

Step 1: Copy column names in the DDL to the text pad and remove the data type and NOT NULL column values to retain only the column names. Place the cursor LHS of "NUMBER(30)" and press Alt + Shift + highlight with the mouse and then press delete to delete the highlighted columns.

Step 2: Click on the non-printing symbols button to see the CR/LF and replace them with "" as shown below. Use CTRL + F to pop up the Find/Replace, and select the Replace tab.

Find: \r\n
Replace:

In Extended mode as shown below.

Step 3: Select the "Regular expression" search mode, and perform

Find: [\s]*
Replace: ,

You need to delete the first and last ",". This result can be now copied and pasted for INSERT INTO ( ....) for the column names.

Next, for the values ...

Step 4: The following regex is used to prefix each column name with ":NEW."

Find: ([^,]*),
Replace: :NEW.\1,

You should now have the values as

:NEW.id,:NEW.code,:NEW.rule_entry_id,:NEW.name,:NEW.effective,:NEW.expires,:NEW.created_by,:NEW.created_date,:NEW.updated_by,updated_date.

You can even convert case with Edit --> Convert Case to


You may also like other Notepad++ and Excel tutorials on developer productivity

• Software developer productivity tip with Notepad++ to construct SQL clause or any other data conversion
• Notepad++ with power of regex as a productivity tool for developers
• Notepad++ plugin for viewing JSON data and other benefits
• Excel spreadsheet to generate SQL

Software developer productivity tip with Notepad++ to construct SQL clause or any other data conversion

Lets take a tutorial like scenario to demonstrate power of Notepad++ as a  developer productivity tool.

Q. How will you extract rule_name values from a tabular data shown below and convert it to an SQL query as shown below as well?

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 com from an excel spread sheet, word document, or copied from a confluence or wiki page.

The SQL we need is:

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.

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    + keys together and highlight the columns you want to remove with the mouse. Do the same for the last column as well.

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.



You may also like:

• Excel spreadsheet to generate SQL
• Notepad++ with power of regex as a productivity tool for developers
• Notepad++ plugin for viewing JSON data and other benefits
• 5 handy eclipse tips you must know
• Eclipse debugging tips tutorial style

Notepad++ with power of regex as a productivity tool for developers

This is an extension to my post entitled Notepad++ plugin for viewing JSON data and other benefits to demonstrate the power of Notepad++ and regular expression.

This post is based on a industrial example where you have a CSV file where you want to add quotes (i.e. ") around it. For example

Convert text like:

 
BA555,04-May-2013,04-04-2013,12358,AUSTRALIAN BONDS,,86776.00,AUD,86776,Application,,Capital

To

"BA555","04-May-2013","04-04-2013","12358","AUSTRALIAN BONDS","","86776.00","AUD","86776","Application","","Capital"

Another reason you might want to do this in Java is to construct a String array or list as shown below.

       List<string> asList = Arrays.asList(new String[]
        {
            "BA555", "04-May-2013", "04-04-2013", "12358", "AUSTRALIAN BONDS", "", "86776.00", "AUD", "86776",
            "Application", "", "Capital"
        });

Now let's see how we can use notepad++ find/replace function to add quotes around each entry using its find/replace with regular expressions as shown below.

As you can see

Find what regular expression is:  ([^,]*)(,?) , which means 0 or more characters but "," as first group stored in "\1" followed by 0 or 1 "," (i.e. optional ,), and grouped as \2.

Replace with regular expression is: "\1"\2 where " is added then followed by \1, which is the value captured like BA555 and then followed by ", and followed by optional ",".

Similar approaches can be used for formatting other bulk records like removing spaces, adding quotes, replacing new line characters with tabs, replacing tabs with new lines, replacing commas with new lines, etc.

Here is an example for replacing "," with new lines.

The output will be

 
BA555
04-May-2013
04-04-2013
12358
AUSTRALIAN BONDS

86776.00
AUD
86776
Application

Capital

Notepad++ plugin for viewing JSON data and other benefits

Q. Why is Notepad++ is a very handy tool?
A.  Notepad++ is a very handy developer tool. Here are some of the productivity benefits of Notepad++.

•  to view source code as a free and light weight source code editor that runs in Windows environment. You can also use it to view log files and other text data.
• It can be used for syntax highlighting, line numbering, search and replace with or without regular expressions, modified file detection, file search, etc. 

Working with JSON data

Recently, I had to work a lot with JSON data, and stumbled across a very handy plugin for Notepadd++ called "JSONViewer Notepad++", which allows you to format JSON data and view them as a tree structure. Here are the simple setps to install this plugin.

Step 1: Download the plugin as a zip file from the source-forge site.

Step 2: Unzip the downloaded file and extract out the "NPPJSONViewer.dll" file.

Step 3: Copy this file to the plugins folder in your Notepad++ installation folder, and restart your Notepad++. for example "c:\myapps\Notepad++\5.9\plugins".

Step 4: When you open up your Notepad++, you will see the "JSONViewer" sub menu under the "Plugins" on the top main menu. You can format the JSON text by highlighting your text first and then clicking on Plugins --> JSON Viewer --> Format JSON.


Extracting data

If you had some tab limited data like

FirstName Surname Age
John  Smith 25
Peter  Warren 35
Lisa  Jenkins 28

and if you want to extract out all the first names, you can invoke the column mode by pressing "Alt" key and then select the "FirstName" column with the mouse selection. Alternatively, you can use the "Alt+Shift+Arrow Key" to select the column entries you need. These options are under the  "Edit --> Column Mode" on the top menu.

Finding and replacing values with Regular expressions

If you have some comma delimited data as shown below

FirstName,Surname,middlename
John,Smith 
Peter,Warren,James 
Lisa,Jenkins

where the middle name is optional, and you want to convert the comma delimited data to tab delimited data.This is where Notepad++ 's regex based find and replace comes in handy.

Step 1: Select "Search --> Find" from the top menu.
Step 2: click on "Replace" tab.
Step 3: Tick "Match case" if required and select "Regular Expression" option.
Step 4: Enter the relevant  regular expressionsfor the "Find What" and "Replace With".


Find What: ([A-Z][a-z]*),([A-Z][a-z]*)(.*)
Replace With: \1\t\2\t\3

Where \1, etc are groups and \t is for tab delimiting.

You can use the "Find In files" tab to replace multiple files. I use this feature to search for files that has certain text (i.e. grep). This is a bulk find and replace capability. It also has other bulk features like "File --> Save all" open files.

Split views, file comparisons and synchronized scrolling

Select  View -->  Move/Clone Current Document to create split views to compare two files side by side. On split views you can select Plugins --> Compare --> compare to highlight the differences or select View --> Synchronize Vertical Scrolling to scroll both views in tandem.

Language feature to highlight reserved key words

Setting the language of the file like Java, JavaScript, etc via Language --> J --> Java or JavaScript will help you easily visually distinguish between functions, reserved words, comments, text, and other types of symbols and expressions in your code.


The latest Notepad++ version should have a TextFx menu to tidy up HTML tags to be XHTML compliant. You can also automate any monotonous tasks you do repeatedly with the Macros. It can also synchronize files with  Subversion (i.e. SVN). Hence Notepad++ is a powerful open-source developer tool that makes you more productive.