Ruby variable in Postgresql Regex

Question

I have a table named orders with a column named comment in it.

I have a list of order ids, and I want to search through all the comment to see which order has referenced them.

So far I have worked out the regex part of the sql

Order.where('comment ~ \'\s\d{5}\' OR comment ~ \'^\d{5}\'')
     .where("comment LIKE '%?%'", order_id)

But I can't seem to work out a way to make the query more flexible to find order_ids that have different length other than 5

Is it possible to make {5} more dynamic, to something like {#{order_id.to_s.length}}?

UPDATE Just ran into this post Using a boundary match would work even better for this case.

Order.where('comment ~ \'\y?\y\'', order_id) Would do the trick.

lusketeer · Accepted Answer · 2017-07-06 12:38:00Z

0

We can pass digit_count

digit_count = order_id.to_s.length
Order.where('comment ~ \'\s\d{?}\' OR comment ~ \'^\d{?}\'', digit_count, digit_count)

if you are looking for specific order_id

Order.where("comment ~ \'\s#{order_id}\s\'")

lusketeer

1,9301 gold badge12 silver badges29 bronze badges

answered Jul 6, 2017 at 10:51

Pardeep Dhingra

3,9467 gold badges35 silver badges57 bronze badges

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

1 Answer 1