There are two field formats. One is SOMENAME1234 and another is SOMENAME1234_SE. What I need to do is select all of the records where the number 1234 is less than X. The application is a test database where Jenkins generates unique schema names to keep our developer's tests from crashing into each other. The value '1234' represents a build number.
What I'm trying to write is the cleanup script. Problem is that I need to make sure that I'm not removing any schemata that are currently in use. So I found a way to pragmatically hit Jenkins for what is currently building. Such that now I have a value X that represents the lowest build number currently building. I can delete any schemata with a value lower than this. Problem is that I don't know how to write that SQL. Looking at REGEXP_LIKE, I see that there might be a regular expression involved.
Currently I have:
SELECT username FROM dba_users where username NOT LIKE '%"+upperBound+"%' AND username LIKE 'BLDXYZPDQ%'
This is the best I've been able to do so far.
What I intend is to select the list and then drop each schema in that list one at a time. That way I need not worry about new schemata created during the process.