Rails SQL regular expression

Question

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]%")

Orabîg · Accepted Answer · 2017-03-13 01:09:01Z

87

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)

edited Mar 13, 2017 at 1:09

Orabîg

12k6 gold badges41 silver badges64 bronze badges

answered Oct 27, 2014 at 12:47

Epigene

3,9541 gold badge32 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marian Theisen Over a year ago

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 !~*)

Sergey Over a year ago

strangely only works if 'regex' but not "regex" (single quotes)

Epigene Over a year ago

Yes, there is an issue with regex in Ruby strings, namely "\d" evaluates to 'd'. Single quotes do seem safer, but preclude interpolation, sadly.

Nuclearman Over a year ago

@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.

Jose Kj Over a year ago

thanks,i had to use double quotes and also for \d i had to use \\d.

davegson · Accepted Answer · 2015-01-12 10:35:38Z

35

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

edited Jan 12, 2015 at 10:35

answered Nov 4, 2013 at 21:55

davegson

8,3515 gold badges55 silver badges72 bronze badges

1 Comment

davegson Over a year ago

Another problem may occur if your SQLite didn't install REGEXP by default. stackoverflow.com/questions/5071601/…

Juan Paulo Pérez-Tejada · Accepted Answer · 2022-07-11 16:29:35Z

2

An alternative solution for this is using the Arel::Predications method matches_regexp.

First of all, you create an Arel table.

users = User.arel_table

And then you perform the query with a regexp

User.where(users[:email].matches_regexp('(.*)\@gmail.com'))

By default, this method does a case sensitive query. If you need to do a case insensitive query, you need to add case_sensitive = false as an option.

Example,

User.where(users[:email].matches_regexp('(.*)\@gmail.com', case_sensitive = false))

This method supports PostgreSQL and MySQL

More info: https://github.com/rails/rails/pull/36800

answered Jul 11, 2022 at 16:29

Juan Paulo Pérez-Tejada

1541 silver badge10 bronze badges

Comments

Andrew Kothmann · Accepted Answer · 2013-11-04 21:55:35Z

-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.

answered Nov 4, 2013 at 21:55

Andrew Kothmann

6173 silver badges4 bronze badges

1 Comment

Yarin Over a year ago

@Xathras - "You can't use regular expressions in SQL"? PostgreSQL and MySQL both support regex

Collectives™ on Stack Overflow

Rails SQL regular expression

4 Answers 4

5 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related