Continuing in my brief series of posts on the wonder of @sed@, this time we will be looking at using @sed@’s addressing features to generate code.

@Sed@ and addressing

As we looked at before, @sed@ can match text based on a regular expression. @sed@ can also store matched text in a series of registers in order to manipulate them. This example swaps two words over:

s/(Hello) (World)/2 1/g

So running the above command on “Hello World” would return “World Hello”. The @( )@ are used to store the text that matched text in a register, numbered 1 – 9. We can then recall the registers using @1@, @2@ etc. The text we match can also be a regular expression. If we wanted to swap any two words over, we could use this:

s/(.*) (.*)/2 1/g

So “Hello World” becomes “World Hello”, “Foo Bar” becomes “Bar Foo” etc.

The scenario

You have some data in a spreadsheet. You need to load it into a database. You can only load it in using SQL. The data looks something like this:

Name Town Colour
Bob London Red
Fred Pairs Blue
Mandy Valencia Green

Creating the SQL

First off save your data as a CSV file, without a header:


Then, write a sed expression that can match each value in turn, replacing the commas with dashes to make sure we are matching the text correctly:


Which should give the result:


Now we know we can reprint the matched text inside other text, so its a simple matter to add some text to update our fictional database:

s/(.*),(.*),(.*)/INSERT INTO  customer VALUES('1','2','3');/g

Which gives us:

INSERT INTO customer VALUES('Bob','London','Red');
INSERT INTO customer VALUES('Fred','Paris','Blue');
INSERT INTO customer VALUES('Mandy','Valencia','Green');

Any language could of been used in this example. I tend to use it most for generating SQL due to the nature of my work, but I have even used it for generating test Java code or XML documents.

Next I’ll look at some more advanced log processing, and who knows, I may even show you how to do these things using Java…