47

I'm trying to search for the maximum number in the series A0001, A0002, A1234, A2351, etc... The problem is that the list I'm searching in also has strings such as AG108939, E092357, AL399, 22-30597, etc...

So basically, I want the Highest A#### value in my database. I was using the following query:

@max_draw = Drawing.where("drawing_number LIKE ?", "A%")

Which was working until numbers such as AG309 started getting in the way because it starts with an A, but has a different format than what I'm looking for.

I'm assuming this should be pretty straight forward with regular expressions, but I'm new to this and don't know how to correctly write this query with a regular expression. Here are some things I've tried that just return nil:

 @max_draw = Drawing.where("drawing_number LIKE ?", /A\d+/)
 @max_draw = Drawing.where("drawing_number LIKE ?", "/A\d+/")
 @max_draw = Drawing.where("drawing_number LIKE ?", "A[0-9]%")
sabrams
  • 1,118
  • 1
  • 15
  • 26

3 Answers3

83

On Rails 4+ with a Postgres database the general form of a RegEx query is:

Model.where("column ~* ?", 'regex')

As for the regex, it can be a general '^A\d+$' or more specific '^A\d{4}$' Breaking it down:

^ - string start anchor
A - literal "A"
\d+ - one or more digits (0-9)
\d{4} - exactly four digits
$ - string end anchor

Basically, the regex reads "the string should start with an A, followed by four digits and then the string should end". The final query line is:

@max_draw = Drawing.where("drawing_number ~* ?", '^A\d{4}$')

Further reading on ruby RegEx at RubyDoc or the more accessible Perl variant (used by Sublime text)

Orabîg
  • 11,475
  • 6
  • 33
  • 54
Epigene
  • 3,320
  • 1
  • 23
  • 31
  • 16
    For the sake of completeness (and because i just ran into that): use `~*` for case insensitive regexp, and `~` for case sensitive. for negation just prepend a bang `!` (`!~` and `!~*`) – Marian Theisen Sep 14 '15 at 13:39
  • strangely only works if 'regex' but not "regex" (single quotes) – Sergey Jan 14 '17 at 03:41
  • Yes, there is an issue with regex in Ruby strings, namely "\d" evaluates to 'd'. Single quotes do seem safer, but preclude interpolation, sadly. – Epigene Apr 04 '17 at 13:42
  • @Epigene Seems like that is because double quotes would be escaped. You could probably just use `('\a' + variable.to_s + '\b')`. the `to_s` probably being important as since it's not interpolation it'll probably error out if it's not sufficently "stringy" with a typecast issue. – Nuclearman Dec 20 '17 at 05:32
  • 1
    This question is tagged with sqlite, so I don't think it's 100% the best answer to that... BUT this was exactly what I needed for postgres so thanks for the answer! – Billy Lazzaro Aug 22 '18 at 14:34
  • thanks,i had to use double quotes and also for \d i had to use \\d. – Jose Kj Apr 29 '20 at 13:11
33

You did a good job! The thing missing was the REGEXP function which is used for regex in queries:

So in your case use

Drawing.where("drawing_number REGEXP ?", 'A\d{4}')
# the {4} defines that there have to be exactly 4 numbers, change if you need to

In SQL you use the '-colons, which is weird because you normally start regex with /-backslashes

davegson
  • 7,882
  • 4
  • 49
  • 69
  • 1
    I tried this and it throws an error: ActiveRecord::StatementInvalid in DrawingsController#new SQLite3::SQLException: near "REGEX": syntax error: SELECT MAX("drawings"."drawing_number") AS max_id FROM "drawings" WHERE (drawing_number REGEX 'A\d{4}') – sabrams Nov 04 '13 at 22:02
  • I had a typo at first, forgot the P at `REGEXP` – davegson Nov 04 '13 at 22:04
  • Another problem may occur if your SQLite didn't install `REGEXP` by default. http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query – davegson Nov 04 '13 at 22:05
  • 1
    Hmmm. It still throws an error, but this time it says: SQLite3::SQLException: no such function: REGEXP – sabrams Nov 04 '13 at 22:06
  • So the above code worked, but just so you know, it started to recognize REGEXP as being valid, but then was having issues writing a good query saying there was a syntax error at A – sabrams Nov 04 '13 at 22:16
  • Hmm, maybe I messed up the `regex`, what did you end up using? – davegson Nov 05 '13 at 12:05
-3

You can't use regular expressions in SQL which is what you're trying to do. Your best bet would be to select just the entries that start with A like your original code, then skip entries that have more than one letter at the beginning.

items = Drawing.where( [ 'drawing_number LIKE ?' , 'A%' ] )
max_value = 0
items.each do |item|
  next if item.drawing_number =~ /\A[A-Za-z]{2,}/
  drawing_number = item.drawing_number.gsub(/\AA/, '').to_i
  max_value = drawing_number if drawing_number > max_value
end

I'm reasonably certain it's possible to get this shorter but this should do what you need.

(\A is the start of line anchor that works with strings containing newlines)

({2,} matches two or more of the proceeding character range)

http://www.rubular.com/ is awesome for testing ruby regexes.

Andrew Kothmann
  • 597
  • 2
  • 4
  • Thanks! Worked like a charm. – sabrams Nov 04 '13 at 22:16
  • 14
    @Xathras - "You can't use regular expressions in SQL"? [PostgreSQL](http://www.postgresql.org/docs/9.0/static/functions-matching.html) and [MySQL](https://dev.mysql.com/doc/refman/5.1/en/regexp.html) both support regex – Yarin Apr 21 '14 at 12:45
  • ^^^ But is it efficient (say sublinear time) to use regex? @Yarin – Nuclearman Dec 20 '17 at 05:34
  • 1
    @Nuclearman I've used regex searches in MySQL that almost as fast as an equality match, even with a large table. It might depend on indexing. – cesoid Jul 25 '21 at 19:18