Rails, Postgres 12, Query where pattern matches regex, and contains substring

Question

I have a field in the database which contains strings that look like: 58XBF2022L1001390 I need to be able to query results which match the last letter(in this case 'L'), and match or resemble the last four digits.

The regular expression I've been using to find records which match the structure is: \d{2}[A-Z]{3}\d{4}[A-Z]\d{7}, So far I've tried using a scope to refine the results, but I'm not getting any results. Here's my scope

def self.filter_by_shortcode(input)
  q = input
  starting = q.slice!(0)
  ending = q
  where("field ~* ?", "\d{2}[A-Z]{3}\d{4}/[#{starting}]/\d{3}[#{ending}]\g")
end

Here are some more example strings, and the substring that we would be looking for. Not every string stored in this database field matches this format, so we would need to be able to first match the string using the regex provided, then search by substring.

36GOD8837G6154231
- G4231
13WLF8997V2119371
- V9371
78FCY5027V4561374
- V1374
06RNW7194P2075353
- P5353
57RQN0368Y9090704
- Y0704

edit: added some more examples as well as substrings that we would need to search by.

Add some example data. What are some rows in your database, what is input, what is the result you're getting, which rows are you expecting to match? — Siim Liiser
– Siim Liiser, Commented Mar 30, 2021 at 20:28
ok, I added some more example strings as well as the input I'd like to use to filter the results. — greyoxide
– greyoxide, Commented Mar 31, 2021 at 15:46

Belayer · Accepted Answer · 2021-04-01 01:27:29Z

1

I do not know Rails, but the SQL for what you want is relative simple. Since your string if fixed format, once that format is validated, simple concatenation of sub-strings gives your desired result.

with base(target, goal) as 
     ( values ('36GOD8837G6154231', 'G4231')
            , ('13WLF8997V2119371', 'V9371')
            , ('78FCY5027V4561374', 'V1374')
            , ('06RNW7194P2075353', 'P5353')
            , ('57RQN0368Y9090704', 'Y0704')
      )
select  substr(target,10,1) || substr(target,14,4) target, goal 
  from base 
 where target ~ '^\d{2}[A-Z]{3}\d{4}[A-Z]\d{7}$';

answered Apr 1, 2021 at 1:27

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Rails, Postgres 12, Query where pattern matches regex, and contains substring

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related