I think it’s fairly typical to have to make a global replace on your database at some time or another – updating old links, old code, ect.  And today this was something I was tasked with!  It was pretty straight forward, and these two stackoverflow questions helped me do it.  To get this working you should just need to follow the commands below, while replacing the following:

• TABLE – The database table you’re replacing content in
• COLUMN – The column in that table you’re replacing content in
• BAD – The old string you want to replace
• GOOD – The new string you want to replace BAD with

1
2
3
4
5
6
7
8
9
10

rails c
 
ActiveRecord::Base.connection.execute(%q{
    update TABLE
    set COLUMN = replace(
        COLUMN,
        'BAD',
        'GOOD'
    )
})

Checking for a string

You could also use the following code to check whether or not the string exists in any entries in that model in the first place. Just swap out MODEL for the model, SEARCH for the string you want to check, and COLUMN for the column you want to search. It will output the total number of entries containing that string, and then loop through their ID numbers.

1
2
3
4
5
6
7
8
9
10
11

rails c
 
a = MODEL.where("COLUMN like ?", "%SEARCH%");0
 
puts a.length
 
puts " "
 
a.all.each do |a|
  puts a.id
end;0

This is just a small twist on the very nice snippet I found from this post.  I just changed it slightly, so you can get all the attributes automatically so you can easily swap out any model.  And it will also list the column names in the first row.

1
2
3
4
5
6
7
8
9
10
11
12

require 'csv'
 
file = "#{Rails.root}/public/data.csv"
 
table = User.all;0
 
CSV.open( file, 'w' ) do |writer|
  writer << table.first.attributes.map { |a,v| a }
  table.each do |s|
    writer << s.attributes.map { |a,v| v }
  end
end