Notepad++ to construct SQL where clause

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

This data could come from an excel spread sheet, word document, or copied from a confluence or wiki page.

The SQL we need is

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.

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.

Top 14 popular posts

500+ Java Interview FAQs

Java & Big Data Tutorials