Jhanvi asked me to help with editing a text file recently. She had a file in the format
'512'
'345'
'876'
and needed to convert it into the format below
INSERT INTO BLAH VALUE ('512');
INSERT INTO BLAH VALUE ('345');
INSERT INTO BLAH VALUE ('876');
There are multiple ways, one can do this. Here is how I did this using Notepad++, an open source text editor. I used the regular expression capability of Notepad++ it’s search and replace function.
- Press “Ctrl + h” to bring up the search and replace window.
- Replace the single quote at the beginning of the line by using ” ^’ “
- Replace the single quote at the end of the line by using ” ‘$ “
Screenshots from the operation
The data in it’s original format
Replacing the first quote mark
Data after the first search and replace operation
Replacing the second quote mark
Data in the final format
Thanks for talking about this topic. I had been searching for good information about it.
Comment by Jon Bocook — February 15, 2010 @ 5:35 pm
how i can replace ip address. for examle 100.29.111.21,
into A.A.A.A?
Comment by roman — February 15, 2011 @ 4:00 am
Roman – I think this might be difficult to do in notepad++. You may be better off using a scripting language (perl, python). Check out this link http://www.regular-expressions.info/examples.html . Hope it helps you.
Comment by Vinay — February 15, 2011 @ 9:48 pm
Difficult? What about using the following regular expression:
[0-9]+.[0-9]+.[0-9]+.[0-9]+
[0-9] means any number
+ means it has to appears at least once
This will find any series of 4 numbers separated by dots, from IP addresses to something like 0.23.1.2323523
Comment by Guillaume — April 12, 2011 @ 4:53 am
Guillaume, thanks, I tried to use that regular expession to find IP-addresses in my log file, but your expression finds not only IP-adresses, it find any series of 4 numbers separated by any symbol (for example “22 09:24:24″), how can I change your expression to search only IP?
Comment by Ruslannnn — September 6, 2011 @ 2:20 am
Ruslan – You can tweak Guillaume’s regex a bit to take care of this exception. You just need to search for . explicitly. See if this modified regex helps
[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+
I tried it with your test data and it did not match the time stamp.
Comment by Vinay — September 6, 2011 @ 8:29 am
Vinay, your expression works correctly.
Thank you!
Comment by Ruslannnn — September 6, 2011 @ 9:47 am