My environment is Ruby 1.8.7-p358 with Rails 2.3.11. I am trying to query for all accounts that have a first_name containing only numbers (they are all 3 digit integers, i.e. 001, 143, 815, etc.). I have been trying to use this query:
Account.find(:all, :conditions => ["first_name LIKE ?", "[^0-9][^0-9][^0-9]"])
All I get in return is => [].
If I use the same query only with NOT LIKE I get all accounts, including the ones where first_name is an integer.
I've also tried using:
Account.find(:all, :conditions => ["first_name REGEXP ?", "[^0-9][^0-9][^0-9]"])
but that only gives me:
ActiveRecord::StatementInvalid: PGError: ERROR: syntax error at or near "REGEXP"
LINE 1: SELECT * FROM "accounts" WHERE (first_name REGEXP '[0-9][0-9...
^
: SELECT * FROM "accounts" WHERE (first_name REGEXP '[0-9][0-9][0-9]') ORDER BY first_name ASC
from /Users/kyle/.rvm/gems/ruby-1.8.7-p358@popcorn_recruiter_portal/gems/activerecord-2.3.11/lib/active_record/connection_adapters/abstract_adapter.rb:227:in `log'
from /Users/kyle/.rvm/gems/ruby-1.8.7-p358@popcorn_recruiter_portal/gems/activerecord-2.3.11/lib/active_record/connection_adapters/postgresql_adapter.rb:520:in `execute'
from /Users/kyle/.rvm/gems/ruby-1.8.7-p358@popcorn_recruiter_portal/gems/activerecord-2.3.11/lib/active_record/connection_adapters/postgresql_adapter.rb:1002:in `select_raw'
from /Users/kyle/.rvm/gems/ruby-1.8.7-p358@popcorn_recruiter_portal/gems/activerecord-2.3.11/lib/active_record/connection_adapters/postgresql_adapter.rb:989:in `select'
from /Users/kyle/.rvm/gems/ruby-1.8.7-p358@popcorn_recruiter_portal/gems/activerecord-2.3.11/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
from /Users/kyle/.rvm/gems/ruby-1.8.7-p358@popcorn_recruiter_portal/gems/activerecord-2.3.11/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all'
from /Users/kyle/.rvm/gems/ruby-1.8.7-p358@popcorn_recruiter_portal/gems/activerecord-2.3.11/lib/active_record/base.rb:665:in `find_by_sql'
from /Users/kyle/.rvm/gems/ruby-1.8.7-p358@popcorn_recruiter_portal/gems/activerecord-2.3.11/lib/active_record/base.rb:1582:in `find_every'
from /Users/kyle/.rvm/gems/ruby-1.8.7-p358@popcorn_recruiter_portal/gems/activerecord-2.3.11/lib/active_record/base.rb:619:in `find'
from (irb):189
from :0
Are regular expressions not allowed? How am I able to find each account with a first_name that's equal to a three digit integer?
The like operator does not evaluate regular expressions. In instead you can use both similar to:
select '980' similar to '[0-9][0-9][0-9]';
?column?
----------
t
Or ~:
select '980' ~ '[0-9][0-9][0-9]';
?column?
----------
t
(1 row)