How to find and remove part of a string in Rails Console - ActiveRecord

Question

I'm looking for a way in the rails console to find any values that finish with a particular string and remove that string from the value.

Something along the lines of:

Model.all.each{|x| x.duration.slice!(" weeks")}.where("duration IS NOT NULL") 

So that values such as "47 weeks" simply become "47", but there isn't a Slice method for ActiveRecord/ActiveModel and I don't know the equivalent.

Any ideas??

Thanks in advance...

Addington

Answer 1

You can use where("column_name LIKE <matcher>") syntax to search for models with the column having the matching substring:

matching_models = Model.where("duration LIKE '% weeks'")

Then iterate over the result, using gsub to replace the column value

matching_models.find_each do |model|
  model.update_column(:duration, model.duration.gsub(/ weeks$/, "")
end

Some relevant points:

• Use find_each instead of each as it will load data in batches to better use memory.

• gsub is used to replace the existing string. The / weeks$/ regex matches strings ending in " weeks".

• model.update_column is used instead of update_attributes to bypass ActiveRecord validations and callbacks. You can use update_attributes if you want to run validations and callbacks.

Sessler

Answer 2

The accepted answer works correctly but will get noticeably slow if you have a few thousand records ending with " weeks" or more, because Rails has to grab and instantiate all matching records from the table.

In general, it is preferable to leave all work to the database engine, if possible, which could be written like this:

Model.where("duration LIKE '% weeks'").
      update_all("duration = SUBSTRING(duration, 1, LENGTH(duration) - 6)")
# => UPDATE `models` SET `models`.`duration` = SUBSTRING(duration, 1, LENGTH(duration) - 6) WHERE (duration LIKE '% weeks')

This query will be much more performant and memory efficient if you have lots of records to update. It uses the update_all Rails method to run the UPDATE query on all matching records in the table and the SUBSTRING function to shorten the duration field by 6 characters, which is the length of the " weeks" string.

Ellynellynn

Answer 3

As @BoraMa says, the accepted answer will take a long time for thousands of records and their answer works for MySQL so here's a PostgreSQL version:

Model.where("duration LIKE '% weeks'").
      update_all("duration = REPLACE(duration, ' weeks', '')")

Rather than trim the last 6 characters this just replaces " weeks" with an empty string. Your question mentions 'finish with a particular string' so here's the regex version which anchors to the end of the string:

Model.where("duration LIKE '% weeks'").
      update_all("duration = REGEXP_REPLACE(duration, ' weeks$', '')")

Cecilia

Answer 4

You can also use column_name.to_i to get 47 from string 47 weeks

Smallage