Query to split using Regexp data split not getting correct result oracle SQL

Question

I am trying to classify the tables into various categories ,but i was not able to get the desired result , please check the below query results , existing results along with the expected results

STANDARD

NON_STANDARD

TEMPORARY

TO_BE_DROPPED

SELECT OWNER,        
       OBJECT_NAME,          
       REGEXP_REPLACE(OBJECT_NAME, '^([A-Z0-9$]{1,})_([A-Z0-9$]{1,})_.*','\1_\2')AS  BEGINNING,          
       REGEXP_SUBSTR(OBJECT_NAME, '[^_]*$') AS ENDING,       
  CASE       
    WHEN REGEXP_LIKE(OBJECT_NAME, '^D(S[CP]?|T)_.+_(T|(W0?[123]?))$')        
   THEN          
     'STANDARD'          
     WHEN REGEXP_LIKE(OBJECT_NAME, '^DB_.+_(D|F|T|W|W1|W2|W3)$')         
       THEN  
     'STANDARD'          
     WHEN REGEXP_LIKE(OBJECT_NAME, '^DE_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3)$')       
  THEN       
     'STANDARD'      
     WHEN OBJECT_NAME LIKE 'TBD%'        
    THEN  
        'TO_BE_DROPPED'  
     WHEN OBJECT_NAME LIKE 'TMP%'        
     THEN 'TEMPORARY_TABLE'      
     WHEN REGEXP_LIKE(OBJECT_NAME, '^DA_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3|A|AD|AM|AQ|AY)$')         
   THEN          
     'STANDARD'          
     ELSE 'NON_STANDARD'         
   END       
   AS TABLE_CLASSIFICATION        
FROM   DBA_OBJECTS
WHERE  OWNER IN ('DI_ABC')  
       AND OBJECT_TYPE = 'TABLE'
GROUP  BY OWNER,         
          OBJECT_NAME        
ORDER  BY OWNER DESC,
          OBJECT_NAME;

EXISTING RESULTS

SNO OWNER  OBJECT_NAME        BEGINNING ENDING  TABLE_CLASSIFICATION 
01  DI_SCC DSC_02_CUST_ORD    DSC_02    ORD      NON_STANDARD
02  DI_SCC DSC_02_CUST_ORD_T  DSC_02    T        NON_STANDARD
03  DI_SCC DSC_02_CUST_ORD_W  DSC_02    W        NON_STANDARD
04  DI_SCC DSC_02_CUST_ORD1   DSC_02    ORD1     NON_STANDARD
05  DI_SCC DSC_02_CUST_ORD_4  DSC_02    4        NON_STANDARD
06  DI_SCC DSP_03_CUST_SHP    DSP_03    SHP      NON_STANDARD
07  DI_SCC DSP_03_CUST_SHP_T  DSP_03    T        NON_STANDARD
08  DI_SCC DSP_03_CUST_SHP_W  DSP_03    W        NON_STANDARD
09  DI_SCC DSP_03_CUST_SHP9   DSP_03    SHP9     NON_STANDARD
10  DI_SCC DSP_03_CUST_SHP_62 DSP_03    62       NON_STANDARD
14  DI_SCC DT_CUST_WRD        DT        WRD      NON_STANDARD
15  DI_SCC DT_CUST_WRD_T      DT        T        NON_STANDARD
16  DI_SCC DT_CUST_WRD_W      DT        W        NON_STANDARD
17  DI_SCC DT_CUST_WRD5       DT        WRD5     NON_STANDARD
18  DI_SCC DT_CUST_WRD_8      DT        8        NON_STANDARD
19  DI_SCC DT_CUST_WRD23      DT        WRD23    NON_STANDARD

EXPECTED RESULTS

SNO OWNER  OBJECT_NAME        BEGINNING ENDING  TABLE_CLASSIFICATION 
01  DI_SCC DSC_02_CUST_ORD    DSC_02    ORD      STANDARD
02  DI_SCC DSC_02_CUST_ORD_T  DSC_02    T        STANDARD
03  DI_SCC DSC_02_CUST_ORD_W  DSC_02    W        STANDARD
04  DI_SCC DSC_02_CUST_ORD1   DSC_02    ORD1     NON_STANDARD
05  DI_SCC DSC_02_CUST_ORD_4  DSC_02    4        NON_STANDARD
06  DI_SCC DSP_03_CUST_SHP    DSP_03    SHP      STANDARD
07  DI_SCC DSP_03_CUST_SHP_T  DSP_03    T        STANDARD
08  DI_SCC DSP_03_CUST_SHP_W  DSP_03    W        STANDARD
09  DI_SCC DSP_03_CUST_SHP9   DSP_03    SHP9     NON_STANDARD
10  DI_SCC DSP_03_CUST_SHP_62 DSP_03    62       NON_STANDARD
14  DI_SCC DT_CUST_WRD        DT        WRD      STANDARD
15  DI_SCC DT_CUST_WRD_T      DT        T        STANDARD
16  DI_SCC DT_CUST_WRD_W      DT        W        STANDARD
17  DI_SCC DT_CUST_WRD5       DT        WRD5     NON_STANDARD
18  DI_SCC DT_CUST_WRD_8      DT        8        NON_STANDARD
19  DI_SCC DT_CUST_WRD23      DT        WRD23    NON_STANDARD

i am trying to correct it in order to get the EXPECTED results , what i need to correct it ?

WHEN REGEXP_LIKE(OBJECT_NAME, '^D(S[CP]?|T)_.+_([A-Z$])$')  

any suggestions what must be modified in the above reg ex statement ?

--this case statement is for schema - DI_STAGE
WHEN REGEXP_LIKE(OBJECT_NAME, '^D(S[CP]?|T)_.+_(T|(W0?[123]?))$')
--this case statement is for schema - DI_ODS
WHEN REGEXP_LIKE(OBJECT_NAME, '^DB_.+_(D|F|T|W|W1|W2|W3)$')
--this case statement is for schema - DI_EDW
WHEN REGEXP_LIKE(OBJECT_NAME, '^DE_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3)$')   
--this case statement is for schema - DI_MART
WHEN REGEXP_LIKE(OBJECT_NAME, '^DE_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3)$')
'^DA_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3|A|AD|AM|AQ|AY)$')

Answer 1

If you really only need to check if the string ends with a numeric value (as per your comment)...

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=66a0e1d6180f3eb1c3b80c06763e8654

CASE WHEN NOT REGEXP_LIKE(OBJECTNAME, '[0-9]+$') THEN 'STANDARD' ELSE 'OTHER' END

EDIT:

Your comment below implies that if the table name doesn't start with DSC_, DSP_ or DT_, then the result should be NONSTANDARD. The simplest way to check for the presence of that would be (DSC|DSP|DT)\_

This gives me...

CASE WHEN REGEXP_LIKE(OBJECTNAME, '^(DSP|DSC|DT)\_(.)*[A-Z]$') THEN 'STANDARD' ELSE 'OTHER' END

To be STANDARD, all of the following must be true

• Must start with DSC_, DSP_ or DT_
• Must end with a capital letter

Until your question is clarified with a more complete and exact set of requirements, this is only my best guess as to what you're trying to achieve.

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=56bdf881655ec8cd3292931303b86ea1

EDIT:

The main difference between where I ended up, and you example, is that you have $ in there twice...

• ([A-Z$])$

Thus, requiring two "end of line".

Change that to [A-Z]$ And it's much the same as I have.