Some time has passed since I started learning Vim. I didn’t wanted to use it at work because I don’t think I’m as productive as I’m with the editor I usually use… till this morning.
Today I was asked to execute about 3000 SQL inserts in a database. The inserts were provided to me in a text file
insert_statements.sql generated from a database at which I have no access. The insert statements looked like the following once:
1 2 3 4 5 6 7 8 9 10 11 12
Customers table had been already populated with other important data, therefore I would have inserted the new data after the existing once.
It was not a SQL problem, because it was sufficient replacing all the
id values (1, 2, …, 3000) with a
CustomersSeq.nextval: a sequence which would have generated the right id values. Instead it was an editing problem! How to replace an always different number for 3000 times?
As a developer you are likely thinking to a program which parses the
insert_statements.sql input file generating a
modified_insert_statements.sql output file. Instead I opened
insert_statements.sql in Vim and I typed:
and that’s all!!! The 3000 statements looked like as I needed:
1 2 3 4 5 6 7 8 9 10 11 12
It took me about 30 seconds in total, not bad :-)
qrjjf(lcwCustomerSeq.nextval<ESC>j0q2999@r is not a wizardry chant, let me explain it.
Opening Vim the cursor is over the
I of the first
INSERT statement. Then I typed:
qrstarts recording a macro (a repeatable set of commands) called
r, which in my mind stands for «replace»;
jjmoves the cursor two lines down, because
jin Vim is like the down arrow key;
f(finds the first
(and move the cursor over it (the cursor is over the
lmoves the cursor one character right, because
lin Vim is like the right arrow key (the cursor is over the first digit of the id value);
cw(change word) deletes the characters from the cursor position to the end of the word (therefore it deletes the id value) and put the editor in «insert» mode;
CustomerSeq.nextvalinserts the text
<ESC>makes the editor exiting from «insert» mode reputing it in «normal» mode;
jmoves the cursor one line down (on the next insert statement);
0moves the cursor to the begin of the line (over the
qstops recording the macro. Now we are in a similar situation like we were just after opening Vim: the cursor is over the
Iof the second
2999@rrepeats 2999 times the «replace» macro.
If you don’t know Vim you might asking why the heck someone should ever learn such strange character sequences. Yes, they seem strange but they are not. In fact, the first learning steps a new Vim user is recommended to do is vimtutor, a basic 30 minutes tutorial, in which you learn much more than the commands described above at points 2..9. Indeed the only commands I used, not cover by
vimtutor, are the once at points 1, 10 and 11, all related to Vim macros.