A simple way to transform and AR state column into a standardized iso code-ish format

update_states.rb

MAPPINGS = {
  # format: "COMMON MISTAKE" => "DESIRED STATE SYMBOL"
  "OHIO" => "OH",
  "CALI" => "CA",
  "CALIFORNIA" => "CA"
  # and so on...
}

STATES = MAPPINGS.values.uniq # or possibly explicitly defined: %w(OH, CA, ...)

table = Foo
column = :state

def update_all table, column, query, new_value
  # possibly look into http://www.postgresql.org/docs/9.3/static/functions-string.html for more SQL string fucs. to hit more cases
  table.where("upper(trim(#{column})) = #{query}").update_all(column => new_value)
end

# update known mappings: Ohio => OH
MAPPINGS.each do |query, state|
  update_all table, column, query, state
end

# cleanup states that are almost in the right format: oh => OH
STATEs.each do |state|
  update_all table, column, state, state
end

# Iterate through all the remaining crap.
loop do
  unknown = table.where.not(column => STATES).first
  break if unknown.nil?
  
  new_state = ask("What state is '#{unknown}'?")
  update_all table, column, unknown, new_state
end