find command search pattern

Question

I have below 4 files

a_ROLLBACK2to3__test.sql,
a_1to2__test.sql,
a_2to3__test.sql,
a_2to2__test.sql

I want to write a find command to return the files a_1to2__test.sql, a_2to3__test.sql and a_2to2__test.sql, the file a_ROLLBACK2to3__test.sql should not be included in the search.

my find command looks like

find . -name "*_*to*__*.sql"

but this returns all files but I don’t want a_ROLLBACK2to3__test.sql.

basically the files with ROLLBACK after the first _ should not be included..

Can anyone help me to write the search pattern for my requirement?

Thanks

Answer 1

Simply filter the results with grep:

find . -name '*_*to*__*.sql' | grep -v ROLLBACK

Or use the AND clause -a with negation !:

find . -name '*_*to*__*.sql' -a ! -name '*ROLLBACK*'

Answer 2

You could simply look for the underscore followed by a digit:

find . -name '*_[0-9]*to*__*.sql'

or for an underscore not followed by R:

find . -name '*_[!R]*to*__*.sql'