Ruby-on-rails Rails SQL 正则表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19777720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails SQL regular expression
提问by sabrams
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...
我正在尝试搜索 A0001、A0002、A1234、A2351 等系列中的最大数字......问题是我正在搜索的列表中也有 AG108939、E092357、AL399、22-30597 等字符串， 等等...
So basically, I want the Highest A#### value in my database. I was using the following query:
所以基本上，我想要我的数据库中的最高 A#### 值。我正在使用以下查询：
@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.
直到诸如 AG309 之类的数字开始妨碍工作，它才起作用，因为它以 A 开头，但格式与我要查找的格式不同。
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:
我假设使用正则表达式应该非常简单，但我对此很陌生，不知道如何使用正则表达式正确编写此查询。以下是我尝试过的一些只返回 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]%")
采纳答案by davegson
You did a good job! The thing missing was the REGEXPfunction which is used for regex in queries:
你做得好！缺少的REGEXP是用于查询中的正则表达式的函数：
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
在 SQL 中，您使用'-colons，这很奇怪，因为您通常以/-backslashes
回答by Epigene
On Rails 4+ with a Postgres database the general form of a RegEx query is:
在带有 Postgres 数据库的 Rails 4+ 上，RegEx 查询的一般形式是：
Model.where("column ~* ?", 'regex')
As for the regex, it can be a general '^A\d+$'or more specific '^A\d{4}$'Breaking it down:
至于正则表达式，它可以是一般的'^A\d+$'或更具体的'^A\d{4}$'分解：
^ - 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:
基本上，正则表达式读取“字符串应以 A 开头，后跟四位数字，然后字符串应结束”。最后的查询行是：
@max_draw = Drawing.where("drawing_number ~* ?", '^A\d{4}$')
Further reading on ruby RegEx at RubyDocor the more accessible Perl variant(used by Sublime text)
进一步阅读RubyDoc上的 ruby RegEx或更易于访问的Perl 变体（由 Sublime text 使用）
回答by Andrew Kothmann
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.
您不能在 SQL 中使用正则表达式，而这正是您想要做的。最好的办法是只选择像原始代码一样以 A 开头的条目，然后跳过开头有多个字母的条目。
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)
（\A 是与包含换行符的字符串一起使用的行锚的开头）
({2,} matches two or more of the proceeding character range)
（{2,} 匹配两个或多个进行中的字符范围）
http://www.rubular.com/is awesome for testing ruby regexes.
http://www.rubular.com/非常适合测试 ruby 正则表达式。
