REGEXP in a where clause I store s3 bucket's path for files in my database. Files can be either pictures(jpg) or pdfs.

Let's suppose I have uploaded photo.jpg to my database via application, path will be like:

/{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/27801124/attachment/4511/main/111/main
/{s3bucket}/filesuser/client/27801125/attachment/4512/file
/{s3bucket}/filesuser/client/27801126/attachment/4513/main


For every file, I have 2 root paths:

/{s3bucket}/filesuser/client/27801125/attachment/4512/file
/{s3bucket}/filesuser/client/27801126/attachment/4513/main
/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main


I need to get, via where clause only the root = 'main'.

Example:

select REGEXP_REPLACE(path_name, '.*/', '') as col2 from seg_table limit 10;


The query works, I get file, main, photo.jpg as result.

How can I include that regexp in a there clause?

Example:

WHERE (select REGEXP_REPLACE(path_name, '.*/', '') as col2 
from seg_table) = 'main'


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'


Can't use '/main$'; as it will give me:

/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main


instead of

/{s3bucket}/filesuser/client/27801124/attachment/4511/main
~ POSIX Regex Operator

The easy way to do this is to use the ~ operator.

SELECT *
FROM ( VALUES
  ( '/{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/27801124/attachment/4511/main/111/main' ),
  ( '/{s3bucket}/filesuser/client/27801125/attachment/4512/file' ),
  ( '/{s3bucket}/filesuser/client/27801126/attachment/4513/main' )
) AS t(path_name)
WHERE path_name ~ '/main$';


You can always be more specific with your regex..

WHERE path_name ~ '/filesuser/client/\d+/attachment/\d+/main$';