Remove all letters from a string column in Rails

Question

I'm writing a migration to change a column type from string to integer.

def change
  change_column :emails, :object_id, :integer
end

This migration is failed because this column already contains string values. Before executing this migration I'm trying to remove all letters from this column so that i can get only integer values. Existing values are like

"AB12345"
"A12345X"
"789X26A"

What script i should execute before migration to remove all letters and achieve only integer values like this?

"12345"
"12345"
"78926"

Thanks

score 2 · Accepted Answer · answered Oct 28 '15 at 14:34

If you have more than say 10,000 records do the conversion in the database itself. For postgres, that'd be something like:

select regexp_replace('1231ASDF12', '[^0-9]', '', 'g')

You can use execute to run raw sql in a migration:

update table set col = regexp_replace(col,  '[^0-9]', '', 'g')

Keep in mind if you're intending object_id to be a foreign key, you'll need to update whatever table is referenced and also ensure you haven't inadvertently broken anything (e.g., if there was AB123 and BC123 in the dataset).

It worked. object_id is not a foreign key. So, its safe to execute this script. Thanks. — Arif, Oct 28 '15 at 14:47

score 0 · Answer 2 · edited May 23 '17 at 12:00

I think you could use the trim function but the folloing line would do just fine as well.

result = Replace("Some sentence containing Avenue in it.", "Avenue", "Ave")

Example from Access VBA | How to replace parts of a string with another string

You could change "A" into "" "B" into "" ect.

You would end up whit a code like this

Do While ActiveCell.Value <>""
        ActiveCell.Value = Replace(ActiveCell.Value, "A", "")
        ActiveCell.Value = Replace(ActiveCell.Value, "B", "")
        ActiveCell.Value = Replace(ActiveCell.Value, "C", "")
        ect...
        ActiveCell.Offset (-1,0).Select
Loop

Remove all letters from a string column in Rails

2 Answers2