Need MySQL RLIKE expression to exclude certain strings ending in particular characters...

Question

So I've been working with RLIKE to pull some data in a new application and mostly enjoying it.

To date I've been using RLIKE queries to return 3 types of results (files, directories and everything).

The queries (and example results) follow:

**All**:

SELECT * FROM public WHERE obj_owner_id='test' AND obj_namespace 
RLIKE '^user/test/public/[-0-9a-z_./]+$' ORDER BY obj_namespace

user/test/public/a-test/.comment
user/test/public/a-test/.delete
user/test/public/directory/
user/test/public/directory/image.jpg
user/test/public/index
user/test/public/site-rip
user/test/public/site-rip2
user/test/public/test-a
user/test/public/widget-test

**Files**:

SELECT * FROM public WHERE obj_owner_id='test' AND obj_namespace 
RLIKE '^user/test/public/[-0-9a-z_./]+[-0-9a-z_.]+$' ORDER BY obj_namespace


user/test/public/a-test/.comment
user/test/public/a-test/.delete
user/test/public/directory/image.jpg
user/test/public/index
user/test/public/site-rip
user/test/public/site-rip2
user/test/public/test-a
user/test/public/widget-test

**Directories**:

SELECT * FROM public WHERE obj_owner_id='test' AND obj_namespace 
RLIKE '^user/test/public/[-0-9a-z_./]+/$' ORDER BY obj_namespace

user/test/public/directory/

This works well for the above 3 basic scenarios but under certain situations I'll be including special 'suffixes' I'd like to be excluded from the results of queries (without having to resort to PHP functions to do it).

A good example of such a string would be:

user/test/public/a-test/.delete

That data (there are more rows then just obj_namespace) is considered deleted and in the Files and All type queries I'd like it to be omitted within the expression if possible.

Same goes for the /.comments and all such meta data will always be in the same format:

/.[sometext]

I'd hoped to use this feature extensively throughout my application, so I'm hoping there might be a very simple answer. (crosses fingers)

Anyway, thanks as always for any/all responses and feedback.

Answer 1

Sounds like you could just add another condition to each query:

... AND obj_namespace NOT RLIKE '/[.][a-z]+$' ...