sqloracle-databaseregexp-substrregexp-like

Oracle Regular Expression-To match exclude numbers

Hi I am trying to exclude the numbers from my case statement in REGEXP_LIKE but i am not getting the desired result

Query

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_NAMING_CONVENTION' 
         WHEN REGEXP_LIKE(OBJECT_NAME, '^DB_.+_(D|F|T|W|W1|W2|W3)$')
           THEN 
         'STANDARD_NAMING_CONVENTION' 
         WHEN REGEXP_LIKE(OBJECT_NAME, '^DE_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3)$')
      THEN 
         'STANDARD_NAMING_CONVENTION'
         WHEN OBJECT_NAME LIKE 'TBD%' 
        THEN 
            'TO_BE_DROPPED' 
         WHEN OBJECT_NAME LIKE 'TMP%' 
        THEN 'TEMPORARY_TABLE' 
         WHEN REGEXP_LIKE(OBJECT_NAME, '^DM_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3|A|AD|AM|AQ|AY)$') 
       THEN 
         'STANDARD_NAMING_CONVENTION' 
         ELSE 'NON_STANDARD_NAMING_CONVENTION' 
       END 
       AS TABLE_CLASSIFICATION 
FROM   DBA_OBJECTS 
WHERE  OWNER IN ('DI_STAGE', 'DI_BDS', 'DI_EDW', 'DI_MART') 
       AND OBJECT_TYPE = 'TABLE' 
GROUP  BY OWNER, 
          OBJECT_NAME 
ORDER  BY OWNER DESC, 
          OBJECT_NAME;

EXPECTED RESULTS

SNO OWNER  OBJECT_NAME       BEGINNING ENDING  TABLE_CLASSIFICATION 
01  DI_BDS DB_PROD_DGGAA_D   DB_PROD   D       STANDARD_NAMING_CONVENTION
02  DI_BDS DB_CUST_DHHA_F    DB_CUST   F       STANDARD_NAMING_CONVENTION
03  DI_BDS DB_DHSHJA_HHSGS_T DB_DHSHJA T       STANDARD_NAMING_CONVENTION
04  DI_BDS DB_DHS_DHHA_W     DB_DHS    W       STANDARD_NAMING_CONVENTION
05  DI_BDS DB_GSG_DHHA_W01   DB_GSG    W01     STANDARD_NAMING_CONVENTION
06  DI_BDS DB_GFS_FHSH_W02   DB_GFS    W02     STANDARD_NAMING_CONVENTION
07  DI_BDS DB_FGS_FHS_W03    DB_FGS    W03     STANDARD_NAMING_CONVENTION
08  DI_BDS DB_DJJ_GSA_W1     DB_DJJ    W1      STANDARD_NAMING_CONVENTION
09  DI_BDS DB_DKS_SJ_W2      DB_DKS    W2      STANDARD_NAMING_CONVENTION
10  DI_BDS DB_DJA_DT_W3      DB_DJA    W3      STANDARD_NAMING_CONVENTION
11  DI_BDS DB_DHH_DG         DB_DHH    DG      NON_STANDARD_NAMING_CONV
12  DI_BDS DB_DNS_DRS_123    DB_DNS    123     NON_STANDARD_NAMING_CONV
13  DI_BDS DB_FHD_DRS_1      DB_FHD    1       NON_STANDARD_NAMING_CONV
14  DI_BDS DB_OKS_DRS_0      DB_OKS    0       NON_STANDARD_NAMING_CONV
15  DI_BDS DB_SKG_DRS_90     DB_SKG    90      NON_STANDARD_NAMING_CONV

Finally Matched Results

Solution

  • You should use () instead of [] and | instead of , as follows:

    REGEXP_LIKE(OBJECT_NAME, '^DB_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3)$')