Java-Success Blog: 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:

Labels: Notepad, Tools