CODEWITHSUNDEEP
ruby-on-railsregexrubypostgresqlactiverecord
Benjamin
Benjamin

Reputation: 571

How to use regex in active record queries?

There are values in a specific column of my database that ends with a number but others that do not. I'm trying to only take the data that is not containing these numbers.

I tried to use these queries but they do not work :

User.where.not("spec like ?", "%\d")
User.where.not("spec ~ ?", "%\d")

How could I find this data ?

Upvotes: 2

Views: 455

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626758

Use SIMILAR TO with %[0-9] pattern:

User.where.not("spec SIMILAR TO ?", "%[0-9]")

The SIMILAR TO operator is similar to regex, but allows the use of wildcards as with LIKE and some "light" regex constructs, e.g. bracket expressions like [0-9] or [A-Z]. The pattern should match the whole input as with LIKE.

So, the %[0-9] pattern will match any strings that start with any text (% wildcard does that) and end with an ASCII digit (due to the [0-9] at the end).

Upvotes: 4

Related Questions