Category: one-liners

Comma separated list of EU country codes

I’ve been doing some work with SQL this afternoon, and needed to query some data for EU countries. I couldn’t easily find a comma separated list of EU-28 2 digit country codes, so here is one for future reference:

'BE','BG', 'CZ', 'DK', 'DE', 'EE', 'IE', 'EL', 'ES', 'FR', 'HR', 'IT', 'CY', 'LV', 'LT', 'LU', 'HU', 'MT', 'NL', 'AT', 'PL', 'PT', 'RO', 'SI', 'SK', 'FI', 'SE', 'UK'

For VAT purposes, the United Kingdom is referred to by GB instead of UK, so you may need this list instead:

'BE','BG', 'CZ', 'DK', 'DE', 'EE', 'IE', 'EL', 'ES', 'FR', 'GB', 'HR', 'IT', 'CY', 'LV', 'LT', 'LU', 'HU', 'MT', 'NL', 'AT', 'PL', 'PT', 'RO', 'SI', 'SK', 'FI', 'SE'

This list was derived from the EU’s Interinstitutional style guide and UK Gov’s VAT EU country codes. These codes are a sub-set of ISO 3166-1 alpha-2 codes, with a few exceptions for UK and Greece.

How to stop Mysql ASCII tables / column separators from being lost when redirecting bash output

Today I needed to write a quick bash script to send a monthly report  to a colleague.  The report required running a few MySQL queries and then concatenating the output into a file,  and e-mailing it to them.  Normally when you run a MySQL query from the command line, the output is shown within a handy ASCII table.

# mysql --table -e "SELECT 1+1";
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

Unfortunately,  it seems that when you redirect the output to a file,  the ASCII formatting is lost…

# mysql -e "SELECT 1+1" > /tmp/test
# cat /tmp/test
1+1
2

It took me a while to find it, but the solution is really simple. Simply add the –table parameter to the MySQL command:

# mysql --table -e "SELECT 1+1" > /tmp/test
# cat /tmp/test
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

And that’s it! Hopefully this post will save someone else some time in the future.

Showing total disk use on Linux – a.k.a How to sum the output of df

If you want to find the total amount of disk space used on Linux, and other Unix based systems (such as OSX), you can do so quite easily with the following one liner…

df -lP | awk '{total+=$3} END {printf "%d G\n", total/2^20 + 0.5}'

What this does is…

  • df -lP … shows a disk report of all local disks, in posix format (e.g. one line per volume)
  • | awk ‘{total+=$3} END {printf “%d G\n”, total/2^20 + 0.5}’ … this takes the output of the df command, pipes it to awk which then sums the 3rd columns into a variable called total, and when it’s finished it prints out this number converted to Gigabytes.  To get to Gigabytes, we divided by 2^20 (1024*1024), and we also add 0.5 so that it is effectively rounded to the nearest whole number.

This is particularly helpful if you have a lot of volumes on a system.