CODEWITHSUNDEEP
ruby-on-rails
Arif
Arif

Reputation: 1399

Remove all letters from a string column in Rails

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

Upvotes: 3

Views: 338

Answers (2)

spike
spike

Reputation: 10004

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).

Upvotes: 2

Cornelis
Cornelis

Reputation: 435

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

Upvotes: 0

Related Questions