Re: regexp_matches where clause - PG 9.1

2017-04-06 18:10 GMT+12:00 Patrick B <patrickbakerbr(at)gmail(dot)com>:

>
> 2017-04-06 17:35 GMT+12:00 Arjen Nienhuis <a(dot)g(dot)nienhuis(at)gmail(dot)com>:
>
>>
>>
>> On Apr 6, 2017 05:57, "Patrick B" <patrickbakerbr(at)gmail(dot)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.