2017-04-06 18:10 GMT+12:00 Patrick B <patrickbake...@gmail.com>:
>
> 2017-04-06 17:35 GMT+12:00 Arjen Nienhuis <a.g.nienh...@gmail.com>:
>
>>
>>
>> On Apr 6, 2017 05:57, "Patrick B" <patrickbake...@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.
