regexp_matches where clause - PG 9.1
Hi guys,
i've got this column:
path_name character varying(255)
I store full S3 bucket path for the attachments of my application on it;
example:
/{s3bucket}/filesuser/client/27801123/attachment/4510/main
/{s3bucket}/filesuser/client/27801123/attachment/4510/file
I wanna do a select, where path_name has only 'main' and not anything else.
Maybe using regexp_matches but then how to put it into a where clause?
Thanks!
Patrick.
On Apr 6, 2017 05:57, "Patrick B" <patrickbakerbr@gmail.com> wrote:
Hi guys,
i've got this column:
path_name character varying(255)
I store full S3 bucket path for the attachments of my application on it;
example:
/{s3bucket}/filesuser/client/27801123/attachment/4510/main
/{s3bucket}/filesuser/client/27801123/attachment/4510/file
I wanna do a select, where path_name has only 'main' and not anything else.
WHERE path_nane LIKE '%/main'
2017-04-06 17:35 GMT+12:00 Arjen Nienhuis <a.g.nienhuis@gmail.com>:
On Apr 6, 2017 05:57, "Patrick B" <patrickbakerbr@gmail.com> wrote:
Hi guys,
i've got this column:
path_name character varying(255)
I store full S3 bucket path for the attachments of my application on it;
example:/{s3bucket}/filesuser/client/27801123/attachment/4510/main
/{s3bucket}/filesuser/client/27801123/attachment/4510/file
I wanna do a select, where path_name has only 'main' and not anything else.
WHERE path_nane LIKE '%/main'
I was able to do it like this:
WHERE (path_name)::text ~
'^\/filesuser\/client/\d+/(attachment)/\d+/(main)+'
Thanks
Patrick.
2017-04-06 18:10 GMT+12:00 Patrick B <patrickbakerbr@gmail.com>:
2017-04-06 17:35 GMT+12:00 Arjen Nienhuis <a.g.nienhuis@gmail.com>:
On Apr 6, 2017 05:57, "Patrick B" <patrickbakerbr@gmail.com> wrote:
Hi guys,
i've got this column:
path_name character varying(255)
I store full S3 bucket path for the attachments of my application on it;
example:/{s3bucket}/filesuser/client/27801123/attachment/4510/main
/{s3bucket}/filesuser/client/27801123/attachment/4510/file
I wanna do a select, where path_name has only 'main' and not anything
else.WHERE path_nane LIKE '%/main'
I was able to do it like this:
WHERE (path_name)::text ~ '^\/filesuser\/client/\d+/(
attachment)/\d+/(main)+'
Thanks
Patrick.
Guys.. sorry but actually it's not exactly what I'm looking for:
The paths */{s3bucket}/filesuser/client/27801123/attachment/4510/main
/{s3bucket}/filesuser/client/27801123/attachment/4510/file* are the root
path for:
/{s3bucket}/filesuser/client/27801123/attachment/4510/file/
1113/small/photo.jpg
/{s3bucket}/filesuser/client/27801123/attachment/4510/main/
111/small/photo.jpg
So for that single file (photo.jpg) i can have:
/{s3bucket}/filesuser/client/27801123/attachment/4510/file/
1113/small/photo.jpg
/{s3bucket}/filesuser/client/27801124/attachment/4511/main/
111/small/photo.jpg
/{s3bucket}/filesuser/client/27801125/attachment/4512/file
/{s3bucket}/filesuser/client/27801126/attachment/4513/main
select REGEXP_REPLACE(path_name, '.*/', '') as col2 from seg_table limit 10;
It works, but I get :
photo.jpg
main
file
When actually I just want the 'main''
How can I include this regexp in a where clause? Something like:
WHERE REGEXP_REPLACE(path_name, '.*/', '')::text = 'main'
Thanks!
Patrick.
On Thu, Apr 6, 2017 at 6:33 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
When actually I just want the 'main''
SELECT * FROM tbl WHERE path_name ~ '/main$' ?
David J.
2017-04-07 14:08 GMT+12:00 David G. Johnston <david.g.johnston@gmail.com>:
On Thu, Apr 6, 2017 at 6:33 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:When actually I just want the 'main''
SELECT * FROM tbl WHERE path_name ~ '/main$' ?
David J.
David,
That won't work.
When performing the select, I got:
/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/main
when actually i want:
/{s3bucket}/filesuser/client/27801123/attachment/4510/main
Patrick.
On Apr 7, 2017 4:08 AM, "David G. Johnston" <david.g.johnston@gmail.com>
wrote:
On Thu, Apr 6, 2017 at 6:33 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
When actually I just want the 'main''
SELECT * FROM tbl WHERE path_name ~ '/main$' ?
David J.
Or just:
SELECT 'main';
On Apr 7, 2017 4:16 AM, "Patrick B" <patrickbakerbr@gmail.com> wrote:
2017-04-07 14:08 GMT+12:00 David G. Johnston <david.g.johnston@gmail.com>:
On Thu, Apr 6, 2017 at 6:33 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:When actually I just want the 'main''
SELECT * FROM tbl WHERE path_name ~ '/main$' ?
David J.
David,
That won't work.
When performing the select, I got:
/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/main
when actually i want:
/{s3bucket}/filesuser/client/27801123/attachment/4510/main
Patrick.
WHERE (path_name)::text ~ '^/filesuser/client/\d+/attachment/\d+/main$'
On Thu, Apr 6, 2017 at 7:15 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
David,
That won't work.
Actually, it works fine, you just keep moving the under-specified problem
space.
I'd suggest creating a self-contained running example that gets you close
and show what the final output should be.
David J.
2017-04-07 14:19 GMT+12:00 David G. Johnston <david.g.johnston@gmail.com>:
On Thu, Apr 6, 2017 at 7:15 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:David,
That won't work.Actually, it works fine, you just keep moving the under-specified problem
space.
I'd suggest creating a self-contained running example that gets you close
and show what the final output should be.David J.
http://sqlfiddle.com/#!15/6d65d
There is an example. Please note the `col2` returns not only 'main'. I need
it to return only main and I need to put it in a where clause. I can't use
~ operator because I will need to do something like:
FROM test1 t1
JOIN another_view AS s
WHERE s.full_path = substr(t1.full_path, char_pos '/file..' to get
/filesuser/client/.../attachment/.../) || 'main'
Thanks
Patrick.