Select records matching subset of integer value

Question

I have a table named User (Created by Devise), and a column school, of type string.

Some example values for school: "0", "1", "012", "023".

I want to select rows that include "0" in the value. Similar to how #include? works in Ruby. E.g.:

"012".include?("0") == true
"1".include?("0") == false

How can I do this with an ActiveRecord query?

If you have integer data type and want to store `012` in it, it would be stored as `12`. Please make sure if `012` and `12` mean same or different to you. — Raman, Dec 06 '15 at 10:26
Also worth pointing out that the examples are using strings `"012".include?("0") == true` — ReggieB, Dec 06 '15 at 10:36
Possible duplicate of [can you use activerecord to find substring of a field? (quick & dirty keyword finder)](http://stackoverflow.com/questions/5044372/can-you-use-activerecord-to-find-substring-of-a-field-quick-dirty-keyword-fi) — Drenmi, Dec 06 '15 at 11:07

score 2 · Answer 1 · edited Dec 06 '15 at 10:21

2

Depends on your database type (MySQL, PostgreSQL..) but basically you can do the following:

to return all values with '0' somewhere in the string:

User.where("school LIKE '%0%'")

to return only values with prefix '0':

User.where("school LIKE '0%'")

edited Dec 06 '15 at 10:21

Andrey Deineko

51,333
10
112
145

answered Dec 06 '15 at 10:15

olevitzky

31
7

This won't work, considering `school` is not a string according to the poster. – Drenmi Dec 06 '15 at 10:26
@Drenmi it should be a string. There is no chance to write `012` "integer" into database :) – Andrey Deineko Dec 06 '15 at 10:30
1

A good idea then is to ask for clarification is a comment. As the question stands, this answer is incorrect. :-) If he actually meant string, it is guaranteed to be a duplicate. – Drenmi Dec 06 '15 at 10:31
ofcourse I assume the school value is string... This was before the question was fixed. But I do not know an integer with 0 prefix in base 10 number... – olevitzky Dec 06 '15 at 10:41
OP clarified that it is actually `string`. :-) – Drenmi Dec 06 '15 at 11:03
so my answer still stands – olevitzky Dec 06 '15 at 12:48

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

0

As @Drenmi states, the database won't store leading zeros in an integer field. If this is a identifier or a phone number where the leading zero has significance you will need to store the data in another field type.

Use a string field to store the numbers. You'll then be able to use a LIKE query to get what you want. In that case the following is one way to do that.

You can use arel and the matches method. See How to do a LIKE query in Arel and Rails?

So:

to return all values with '0' somewhere in the string:

users = User.arel_table
User.where(users[:school].matches('%0%'))

to return only values with prefix '0':

users = User.arel_table
User.where(users[:school].matches('0%')

edited May 23 '17 at 12:15

Community

1
1

answered Dec 06 '15 at 10:25

ReggieB

8,100
3
38
46

This won't work, considering `school` is not a string according to the poster. – Drenmi Dec 06 '15 at 10:27
That will teach me for not reading the question more carefully. I'll modify my answer to suit. Thank you @Drenmi – ReggieB Dec 06 '15 at 10:29
It is very possible that the poster made a mistake though, as I don't think you can store leading zeroes in integer fields. :-) – Drenmi Dec 06 '15 at 10:30
@Drenmi I'm sure you are right. If they want to test for the presence of the zero, they'll need it to be present. I think I've updated my post to cover that. – ReggieB Dec 06 '15 at 10:35
OP clarified that it is actually `string`. :-) – Drenmi Dec 06 '15 at 11:04

Select records matching subset of integer value

2 Answers2