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

To, following INSERT statement in the trigger

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

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