Postgres Regex to escape dollar sign

Question

I'm using Rails/ActiveRecord/Postgres. Here is a sample test string I'm trying to match -

:sample_data => "$$352054054640611,1010,2015/02/24,05:20:30,40.28370,-111.65530,1437.1,0,0,0,0,0.0,7,0.9,1010,0.0,12.2,8,1,0,81.3##"

I only want to query for the first number after the first comma (ex: 1010). In Rails I was looking to do something like this.

number = "1010"
Model.where("sample_data ~* ?", "\$\$.*," + number + ",.*")

The problem is it looks like Postgres isn't allowing me to escape the two dollar signs like I hoped.

Edit

When I run Model.where("sample_data ~* ?", "\$\$.*," + number + ",.*").to_sql

I get -

"SELECT \"Model\".* FROM \"Model\"  WHERE (sample_data ~* '$$.*,1010,.*')"

Can you add the result of Model.where("sample_data ~* ?", "\$\$.*," + number + ",.*").to_sql — max
– max, Commented Feb 10, 2016 at 19:52
Hmm, maybe you need to double escape? Model.where("sample_data ~* ?", "\\$\\$.*," + number + ",.*") — max
– max, Commented Feb 10, 2016 at 20:00
@max the double escapes work! The problem I'm having now is match the first set of digits - Model.where("sample_data ~* ?", "\\$\\$(\\c+),1010,.*") — Ryan Grush
– Ryan Grush, Commented Feb 10, 2016 at 20:17

PJSCopeland · Accepted Answer · 2016-02-10 21:49:15Z

2

The answer to your question is, you need escape the backslashes. You're wanting to pass them in to Postgres, but Ruby is escaping the dollars instead. (It doesn't need to, because '$' isn't a special character in a Ruby string, but it'll let you do it anyway.)

Model.where("sample_data ~* ?", "\\$\\$.*," + number + ",.*").to_sql

The answer to your problem is, you probably don't need to specify the dollars at all. Try this:

Model.where("sample_data ~* ?", "^[^,]*,#{number}").to_sql

To help you interpret this regex:

^ = start of line
[^,] = any character other than a comma
* = zero to many times

This will also ensure that you don't find your number after a comma that isn't the first.

answered Feb 10, 2016 at 21:49

PJSCopeland

3,0361 gold badge31 silver badges44 bronze badges

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

Collectives™ on Stack Overflow

Postgres Regex to escape dollar sign

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