CODEWITHSUNDEEP
regexpostgresqlruby-on-rails-2
starscream_disco_party
starscream_disco_party

Reputation: 3006

Query for only integers Rails 2.3

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?

Upvotes: 0

Views: 288

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125564

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)

Upvotes: 2

Related Questions