6

If I have a number (such as 88) and I want to perform a LIKE query in Rails on a primary ID column to return all records that contain that number at the end of the ID (IE: 88, 288, etc.), how would I do that? Here's the code to generate the result, which works fine in SQLLite:

@item = Item.where("id like ?", "88").all

In PostgreSQL, I'm running into this error:

PG::Error: ERROR:  operator does not exist: integer ~~ unknown

How do I do this? I've tried converting the number to a string, but that doesn't seem to work either.

scottdevries
  • 117
  • 2
  • 7

4 Answers4

14

Based on Erwin's Answer:
This is a very old question, but in case someone needs it, there is one very simple answer, using ::text cast:

Item.where("(id::text LIKE ?)", "%#{numeric_variable}").all

This way, you find the number anywhere in the string.
Use % wildcard to the left only if you want the number to be at the end of the string.
Use % wildcard to the right also, if you want the number to be anywhere in the string.

Ruby Racer
  • 5,690
  • 1
  • 26
  • 43
8

Simple case

LIKE is for string/text types. Since your primary key is an integer, you should use a mathematical operation instead.

Use modulo to get the remainder of the id value, when divided by 100.

Item.where("id % 100 = 88")

This will return Item records whose id column ends with 88

1288
1488
1238872388
862388

etc...

Match against arbitrary set of final two digits

If you are going to do this dynamically (e.g. match against an arbitrary set of two digits, but you know it will always be two digits), you could do something like:

Item.where(["id % 100 = ?", last_two_digits)

Match against any set or number of final digits

If you wanted to match an arbitrary number of digits, so long as they were always the final digits (as opposed to digits appearing elsewhere in the id field), you could add a custom method on your model. Something like:

class Item < ActiveRecord

  ...

  def find_by_final_digits(num_digits, digit_pattern)
    # Where 'num_digits' is the number of final digits to match
    # and `digit_pattern` is the set of final digits you're looking fo

    Item.where(["id % ? = ?", 10**num_digits, digit_pattern])
  end

  ...

end

Using this method, you could find id values ending in 88, with:

Item.find_by_final_digits(2, 88)

Match against a range of final digits, of any length

Let's say you wanted to find all id values that end with digits between 09 and 12, for whatever reason. Maybe they represent some special range of codes you're looking up. To do this you could do another custom method to use Postgres' BETWEEN to find on a range.

def find_by_final_digit_range(num_digits, start_of_range, end_of_range)
  Item.where(["id % ? BETWEEN ? AND ?", 10**num_digits, start_of_range, end_of_range)
end

...and could be called using:

Item.find_by_final_digit_range(2, 9, 12)

...of course, this is all just a little crazy, and probably overkill.

jefflunt
  • 33,527
  • 7
  • 88
  • 126
  • Great answer! Do you know if SQLite supports the modulo query? – scottdevries Nov 30 '12 at 23:59
  • 1
    @scottdevries: Yes, it does. -> [sqlfiddle](http://www.sqlfiddle.com/#!5/b1698/6) – Erwin Brandstetter Dec 01 '12 at 00:00
  • 3
    If you're asking because you're using SQLite in development, but Postgres in production, it's really not that much work to get Postgres working in development as well. If you start writing any custom SQL in your app you'll want to be using the same flavor in both environments to avoid surprises when you try to deploy. – jefflunt Dec 01 '12 at 00:02
  • @normalocity: You're absolutely right - I'll get right on it. Getting tired of pushing to a test server every 5 minutes. – scottdevries Dec 01 '12 at 00:04
  • 2
    +1 on using Postgres in development and production. Saves you a lot of headache. Check out [this related question](http://stackoverflow.com/questions/11249059/generic-ruby-solution-for-sqlite3-like-or-postgresql-ilike) as another example, why mixing is a bad idea. – Erwin Brandstetter Dec 01 '12 at 00:05
  • @scottdevries - Getting your feedback loops as small as possible helps you develop faster. I would imagine pushing to a test server every five minutes would, indeed, get quite tiring. – jefflunt Dec 01 '12 at 00:07
  • Ok, got your third example to work, but you have a typo. You want to square 10 by the number of digits, not multiply. Might want to fix that for posterity. – scottdevries Dec 01 '12 at 00:21
  • Thanks, fixed it. Good eye. `**` operator to the rescue! – jefflunt Dec 01 '12 at 00:25
5

The LIKE operator is for string types only.
Use the modulo operator % for what you are trying to do:

@item = Item.where("(id % 100) = ?", "88").all

I doubt it "works" in SQLite, even though it coerces the numeric types to strings. Without leading % the pattern just won't work.
-> sqlfiddle demo

Cast to text and use LIKE as you intended for arbitrary length:

@item = Item.where("(id::text LIKE ('%'::text || ?)", "'12345'").all

Or, mathematically:

@item = Item.where("(id % 10^(length(?)) = ?", "'12345'", "12345").all
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

LIKE operator does not work with number types and id is the number type so you can use it with concat

SELECT * FROM TABLE_NAME WHERE concat("id") LIKE '%ID%'
Chandrika Shah
  • 638
  • 6
  • 6