I’ve been doing a lot of data clean up recently and writing quick Ruby tools that parse array’s of data hashes to produce SQL statements that can be run on databases. I quite like this version of Ruby’s string interpolation so thought I’d share it here in case you’ve not come across it

Given an example piece of SQL that you need to produce in lots of variations like the below

UPDATE `table` SET field_name = 'value' WHERE other_field = 'other_value';

you could iterate over all the data and write this out each time for each variation

data = [
    {table: 'people', field_name: 'name', value: '1', other_field: 'something', other_value: '2'}
]

data.each do |d|
    puts "UPDATE `#{d[:table]}` SET #{d[:field_name]} = '#{d[:value]}' WHERE #{d[:other_field]} = '#{d[:other_value]}';"
end

There’s nothing wrong with this way of doing the string interpolation and then outputting your final strings somehow, but I find the operation to be a bit hard to read. Looking for a better way to do this kind of iterative interpolate I started playing around with the % string interpolation operator and want to offer this as a better solution to the above.

data = [
    {table: 'people', field_name: 'name', value: '1', other_field: 'something', other_value: '2'}
]
template ="UPDATE `%{table}` SET %{field_name} = '%{value}' WHERE %{other_field} = '%{other_value}';"

data.each { |d| puts template % d }

In this case the template is slightly easier to read (IMO), in that each replacement token is just surrounded by {} and prefixed with % rather than the extra Hash notation.

I don’t know if this has a performance overhead, or other implications as I’m still exploring it, but wanted to share a slightly different way of using string templates for interpolation rather an setting each replacement directly.

One of the major benefits I’ve seen in this way of doing it is that replacements are reusable, for example:

data = {
    category: 'test',
    person_id: 1;
}

template = "UPDATE `table` SET person_id = %{person_id}, category = '%{category}' WHERE category = '%{category}--%{person_id}';"

sql = template % data

The last example is contrived for the purpose of this post, but it’s very close to something I had to use recently to correct some data based on updates to an application I was working on, and I hope it shows how you can reuse the same parameters throughout the string very easily.