Rails SQL 正则表达式 [英] Rails SQL regular expression

问题描述

我正在尝试搜索 A0001、A0002、A1234、A2351 等系列中的最大数字......问题是我正在搜索的列表中也有诸如 AG108939、E092357、AL399 之类的字符串，22-30597等...

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

所以基本上，我想要数据库中最高的 A#### 值.我正在使用以下查询:

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

直到 AG309 之类的数字开始妨碍它之前一直有效，因为它以 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.

我假设使用正则表达式应该很简单，但我是新手，不知道如何使用正则表达式正确编写此查询.以下是我尝试过的一些仅返回 nil 的方法:

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 ?", /Ad+/)
 @max_draw = Drawing.where("drawing_number LIKE ?", "/Ad+/")
 @max_draw = Drawing.where("drawing_number LIKE ?", "A[0-9]%")

推荐答案

你做得很好！缺少的是用于查询中的正则表达式的 REGEXP 函数:

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

所以在你的情况下使用

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

在 SQL 中，您使用 '-colons，这很奇怪，因为您通常以 /-backslashes

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

这篇关于Rails SQL 正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！