How do I select a string valued field against a number less than X?

Question

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.

Answer 1

Since the format is not specified exactly, I will just assume the usernames follow the above pattern without any special cases. Find the number:

select to_number(replace(replace('SOMENAME1234_SE', 'SOMENAME', ''), '_SE', '')) as build_number from dual;

BUILD_NUMBER
------------
        1234

Generate commands for dropping users:

select 'drop user ' || username || ' cascade;' from dba_users where username like 'SOMENAME%' and to_number(replace(replace(username, 'SOMENAME', ''), '_SE', ''))  < :X;

Or execute them in PL/SQL:

declare
  x number := 1234;
begin
  for cmd in (select 'drop user ' || username || ' cascade' as text from dba_users where username like 'SOMENAME%' and to_number(replace(replace(username, 'SOMENAME', ''), '_SE', ''))  < x)
  loop
    execute immediate cmd.text;
  end loop;
end;
/