Shell Scripting Madness

Every now and then I bask in the beauty of the simple things. I'm not talking about children smiling, flowers, or any of that other crap. Shell scripting, baby! Today I had to move some SQL statements in some XML document into a Java class. So I needed to change this (which I didn't write):

SELECT CASE
    WHEN primaryStartAge < 20  THEN ' 0 to 19'
	WHEN primaryStartAge BETWEEN 20 AND 29 THEN '20 to 29'
	WHEN primaryStartAge BETWEEN 30 AND 39 THEN '30 to 39'
	WHEN primaryStartAge BETWEEN 40 AND 49 THEN '40 to 49'
	WHEN primaryStartAge BETWEEN 50 AND 59 THEN '50 to 59'
	WHEN primaryStartAge BETWEEN 60 AND 69 THEN '60 to 69'
	WHEN primaryStartAge > 70 THEN '70 and up'
	END as "Primary Start Age Range",
	count(1) as "Count" FROM analyticsResults
	WHERE calculatorType like ?
	GROUP BY CASE
	WHEN primaryStartAge < 20  THEN ' 0 to 19'
	WHEN primaryStartAge BETWEEN 20 AND 29 THEN '20 to 29'
	WHEN primaryStartAge BETWEEN 30 AND 39 THEN '30 to 39'
	WHEN primaryStartAge BETWEEN 40 AND 49 THEN '40 to 49'
	WHEN primaryStartAge BETWEEN 50 AND 59 THEN '50 to 59'
	WHEN primaryStartAge BETWEEN 60 AND 69 THEN '60 to 69'
	WHEN primaryStartAge > 70 THEN '70 and up'
END
ORDER BY 1 ASC

to something like this (which I still didn't write):

"SELECT CASE "
            + "    WHEN primaryStartAge < 20  THEN ' 0 to 19' "
            + "    WHEN primaryStartAge BETWEEN 20 AND 29 THEN '20 to 29' "
            + "    WHEN primaryStartAge BETWEEN 30 AND 39 THEN '30 to 39' "
            + "    WHEN primaryStartAge BETWEEN 40 AND 49 THEN '40 to 49' "
            + "    WHEN primaryStartAge BETWEEN 50 AND 59 THEN '50 to 59' "
            + "    WHEN primaryStartAge BETWEEN 60 AND 69 THEN '60 to 69' "
            + "    WHEN primaryStartAge > 70 THEN '70 and up' "
            + "    END as \"Primary Start Age Range\", "
            + "    count(1) as \"Count\" FROM analyticsResults "
            + "    WHERE calculatorType like ? "
            + "    GROUP BY CASE "
            + "    WHEN primaryStartAge < 20  THEN ' 0 to 19' "
            + "    WHEN primaryStartAge BETWEEN 20 AND 29 THEN '20 to 29' "
            + "    WHEN primaryStartAge BETWEEN 30 AND 39 THEN '30 to 39' "
            + "    WHEN primaryStartAge BETWEEN 40 AND 49 THEN '40 to 49' "
            + "    WHEN primaryStartAge BETWEEN 50 AND 59 THEN '50 to 59' "
            + "    WHEN primaryStartAge BETWEEN 60 AND 69 THEN '60 to 69' "
            + "    WHEN primaryStartAge > 70 THEN '70 and up' "
            + "END "
            + "ORDER BY 1 ASC";

I could copy and paste and fix it manually, use a text editor with regex search and replace, or something equally bland. Since it was Friday though i decided to treat myself and do it from a Cygwin shell. This got me close enough and made me giddy with satisfaction:

getclip |sed -e 's/"/\\"/g' -e 's/^/"/g' -e 's/$/ " +/g' |putclip

This grabs the contents of the clipboard, replaces all quotes with escaped quotes, replaces the beginning of each line with a double quote, and replaces the end of each line with a space / double quote / space / plus combo. It then sticks it back into the clipboard. It's not fancy, it could be better, but it was a minor bright point. And thanks to Cygwin it happened in Windows. Sort of.

Share

Leave a Reply