ORACLE SQL: Build a CASE statement with SUBSTR

Question

The Select statement below is returning the columns attached. At this point everything is good, but I still need to add a few checks. I need to:

• IF the TEXT column contains a period (.) sign, means we have a object_owner/object_name THEN show the owner
• IF the column TEXT doesn't contain a period (.) sign, means this is a RULE and THEN should appear only in OBJECT_NAME without the part (only RULE name : ex: " R_FIRST" becomes just "R_FIRST") and OBJECT_OWNER should not contain anything (just blank and not showing like now (NULL));

Current query:

    SELECT
    SUBSTR(SUBSTR(TRIM(X.TEXT), 1, INSTR(TRIM(X.TEXT), '.')-1), 8) AS OBJECT_OWNER,
    SUBSTR(TRIM(X.TEXT), INSTR(TRIM(X.TEXT), '.')+1) AS OBJECT_NAME,
    TRIM(X.TEXT) TEXT
    FROM 
    TABLE_X X
    WHERE 
    SUBSTR(TRIM(X.TEXT), 1, 6) in ('<FROM>','<INTO>','<RULE>');

Current Output:

    OBJECT_OWNER     OBJECT_NAME       TEXT
    SCHEMA100        T_PERSON          <INTO> SCHEMA100.T_PERSON
    SCHEMA110        T_CLIENT          <FROM> SCHEMA110.T_CLIENT
    (NULL)           <RULE> R_FIRST    <RULE> R_FIRST

Desired Output:

    OBJECT_OWNER     OBJECT_NAME       TEXT
    SCHEMA100        T_PERSON          <INTO> SCHEMA100.T_PERSON
    SCHEMA110        T_CLIENT          <FROM> SCHEMA110.T_CLIENT
                     R_FIRST           <RULE> R_FIRST

Answer 1

SELECT
    CASE WHEN INSTR(X.TEXT, '.') > 1 
         THEN SUBSTR(TRIM(X.TEXT), 8, INSTR(X.TEXT, '.') - 1) 
         ELSE NULL END AS OBJECT_OWNER,
    CASE WHEN INSTR(X.TEXT, '.') > 1 
         THEN SUBSTR(TRIM(X.TEXT), INSTR(X.TEXT, '.') + 1), LEN(TRIM(X.TEXT))) 
         ELSE SUBSTR(TRIM(X.TEXT), 8, LEN(TRIM(X.TEXT))) END AS OBJECT_NAME,
    X.TEXT
FROM TABLE_X X
WHERE SUBSTR(TRIM(X.TEXT), 1, 6) in ('<FROM>','<INTO>','<RULE>')

What this does is it looks in the string to see if the '.' character appears. If it does, then the string position will be greater than 1. If it doesn't, then the condition is not satisfied and it goes to the ELSE statement.

For the OBJECT_NAME with a period in it, you have to start parsing just after the period. Otherwise, you start at character 8 to account for your label tag. In both cases, you can use the full length of the string because if you get to the end it will just stop anyway.

Also, in most cases a null value is more useful than a null string. Make sure you REALLY want '' instead of NULL for your ELSE statement in OBJECT_OWNER. If you run this like it is, the NULL value SHOULD display as a blank entry. I'm not sure why you were getting (NULL) in your previous result set.