Topic: Regular Expressions in DB queries

Howdy folks,

As usual, I am trying to let Rails make my life easier. Today's hurdle is dealing with legacy databases.

http://www.railsguru.com/articles/2006/ … ils-part-1

Using the above technique as inspiration, I will be attempting to connect to my old database.

Thing is, I dont want to import all the data, only rows whose Section column equals A-Z or the number 1.

Even more poking at google reveals that my answer is to rock some regex in my MySQL query. Is this the right way to pull this off?

[code=Ruby"]
old_entries = OldEntry.find(:all, :conditions => 'Section REGEXP '[abcdefghijklmnopqrstuvwxyz1]'')

for o in old_entries do
  n = Entry.new
  n.title = o.title
  n.body = o.body
  n.save
end
[/code]

I am still getting my head around both SQL syntax and REGEX so be gentle smile

Last edited by pimpmaster (2007-03-30 15:22:57)

Re: Regular Expressions in DB queries

Well it turns out I dont need to use regular expressions at all.

old_entries = OldEntry.find_by_sql(SELECT * FROM `legacy_table` WHERE Section IN ('1', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'))

Sure it could probably be prettier, but it works and I only need to use it once.

How cool am I?

Re: Regular Expressions in DB queries

You could shorten it to this:

old_entries = OldEntry.find(:conditions => ['Section IN (?)', ['1'] + ('a'..'z').to_a])

I'll be doing a screencast in a week or so that talks about supplying odd conditions like this. Keep an eye out for that.

Railscasts - Free Ruby on Rails Screencasts

Re: Regular Expressions in DB queries

That's pretty slick. I look forward to your screencast on weird queries. I think that the concept I struggle with most is dealing with arrays and databases. It never would have occured to me to add that ['1'] array to the a..z range, which you have cleverly popped into an array.