I am continuing work on hacking through a jungle of data today and have discovered another useful trick. My standard approach is to have a file open in which I can write notes and also compose extended SQL expressions, copying and pasting between that and the terminal in which I have the mysql interface open. I wanted to write a series of short queries to examine the range of data held in each field of a particular table; it was easy enough to get a list of the fields with the SHOW COLUMNS command but that is cluttered with lots of extraneous information.

Vim has a shell feature, where you can type a range of lines, a bang character (!) followed by a system command. In the past, I have often used this to sort a list (eg. :3,20!sort) but it struck me that other tools could also come in handy. First I tried cut (:3,20!cut -c 2-15), which picked out the bits I wanted but left the shorter ones trailed by unwanted spaces and then I remembered the power of awk (:3,20!awk ‘{print $2}’), which treated each line as an array of data separated by white space and printed out the second item, the field name, from each row.

Magic! That is perfectly in line with the Unix philosophy of using simple tools that can hook together to perform complex operations. I am almost embarrassed to mention it because I should have moved beyond just using sort as an extension years ago but sometimes you can miss the pair of glasses at the end of your nose! Anyway, that hurdle cleared by a mighty bound, back to the SQL.