Regular Expression feature in Notepad++ (And some other advance Text Editor)

无所谓,趁机来这里 update 一下,

Last time I receive a list of mobile number in .csv file, I need to insert those mobile numbers into temporary table in SQL Server.

The data in .csv file looks like below:


There are few ways to do that, one of the method is using DTS (Or SSIS in MSSQL 2005) to transfer data. However I need to login to Remote Desktop (RD), transfer the .csv file to remote server to do that.

And that day the only two slots for RD already used by my colleagues. So I need to figure out other way.

Ok, I was thinking this way:

  1. First, since I can connect to remote SQL Server from my local PC, so I can create a temp table from my PC using create table script.
  2. After I got temp table, I can run the INSERT INTO [TEMP_TABLE] (mobile) VALUES (‘601XXXXXXXX’) statement from local PC.
  3. Duplicate the insert statement in # 2 for all mobile numbers, and run it from SQL Query.
  4. Now I will have a temp table with all mobile numbers inside, no need DTS or SSIS.

Step 1 is not an issue for me, but when come to step 2, I need to append the word INSERT INTO [TEMP_TABLE](mobile) VALUES (‘<mobile-number-in-each-row>‘) to every rows in the .csv file.

So the best way is to replace mobile numbers in each row with the BOLD words above, ok, replace … , replace … , replace function in Notepad++ (Editor I using now), then I press CTRL + H after open Notepad++.

Ehh~~?!, why the Notepad++ search box has one option to pick the search mode for “Regular Expression”? Arrrhh~~~~, I knew it, maybe the Notepad++ author already foresee the situation I facing now.

That is great, I make a quick search on, then give a try to my .csv file, haha, it work now.


  1. Open Notepad++, then press CTRL + H.
  2. Key in “(601[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9])” in “Find What” textbox.
  3. Key in “INSERT INTO [0_TEMP] VALUES(‘\1’)” in “Replace With” textbox.
  4. Choose “Regular Expression” in “Search Mode”.
  5. Click “Replace All” button.
  6. Done, yeah.

Below is how the search box should look like (may vary on different version):

Regular expression replace feature in Notepad++Regular expression replace feature in Notepad++

The final outcome will look like this:

INSERT INTO [0_TEMP] VALUES(‘60121234567’)
INSERT INTO [0_TEMP] VALUES(‘60122345678’)
INSERT INTO [0_TEMP] VALUES(‘60133456789’)
INSERT INTO [0_TEMP] VALUES(‘60164567890’)
INSERT INTO [0_TEMP] VALUES(‘60195678901’)
INSERT INTO [0_TEMP] VALUES(‘60140011223’)

Just a brief explanation to the regexp pattern above (easy for me for future reference):

  1. The pattern I using now only support by Notepad++, it is not global regexp pattern.
  2. Pattern enclose in “(” and “)” is consider as single parameter that going to be replace later.
  3. Notepad++ can recognize the target parameter by using \1, \2, \3 until \9. In my case I only use “\1”.

== [ End ] ==

Words after end: If notepad++ has the features: 1) Column edit, and 2) Hex Edit, then I will vote Notepad++ as the best editor, currently I will say UltraEdit is the best although the interface is qute ugly.

Search Keyword: Tips, Tricks, Notepad++, Tools,  Regular expression, regexp, text editor.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s