SQL> SELECT TRIM ('  mystring  ') FROM dual;

TRIM('MY
--------
mystring

SQL> SELECT TRIM (LEADING 'x' FROM 'xxmystringxx') FROM dual;

TRIM(LEADI
----------
mystringxx

SQL> SELECT TRIM (TRAILING 'x' FROM 'xxmystringxx') FROM dual;

TRIM(TRAIL
----------
xxmystring

SQL> SELECT TRIM (BOTH 'x' FROM 'xxmystringxx') FROM dual;

TRIM(BOT
--------
mystring

SQL> SELECT TRIM ('x' FROM 'xxmystringxx') FROM dual;

TRIM('X'
--------
mystring

SQL> select * from dual where not lnnvl(1=1);
select * from dual where not lnnvl(1=1)
                                    *
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL> select * from dual;
ERROR:
ORA-03114: not connected to ORACLE

Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives

In Oracle, NULL does not equal NULL:

  EDDIE@XE> SELECT 'hi there'
    2    FROM DUAL
    3   WHERE NULL = NULL;

  no rows selected

But sometimes, if not most of the times, you do want the condition NULL = NULL to return true. For example:

  EDDIE@XE> CREATE TABLE t (
    2  col1  VARCHAR2(10),
    3  col2  VARCHAR2(10));

  Table created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', NULL);

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', 'chicken');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', 'monkey');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES (NULL, 'monkey');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES (NULL, NULL);

  1 row created.

  EDDIE@XE> COMMIT;

  Commit complete.

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t;

  COL1       COL2
  ---------- ----------
  monkey     I am null
  monkey     chicken
  monkey     monkey
  I am null  monkey
  I am null  I am null

Now, I want to select all the records from table t where col1 equals col2:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE col1 = col2;

  COL1       COL2
  ---------- ----------
  monkey     monkey

As expected, the row that has NULL in both col1 and col2 was not returned. You want this record returned. Here is how you can do it:

Using the undocumented function SYS_OP_MAP_NONNULL:

This function has been around for a while, but is still undocumented as of the latest Oracle database release (10gR2). Being undocumented means that it should not be used in production code, but it does not hurt to know that it exists and explore how it works. This function makes it possible to have NULL = NULL:

  EDDIE@XE> SELECT sys_op_map_nonnull (NULL)
    2    FROM DUAL;

  SY
  --
  FF

  EDDIE@XE> SELECT 'hi there'
    2    FROM DUAL
    3   WHERE sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL);

  'HITHERE
  --------
  hi there

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE sys_op_map_nonnull(col1) = sys_op_map_nonnull(col2);

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using simple logical operators:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE (col1 = col2 OR (col1 IS NULL AND col2 IS NULL));

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using DECODE:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE DECODE (col1, col2, 'match', 'no match') = 'match';

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using NVL:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE NVL (col1, 'impossible value') = NVL (col2, 'impossible value');

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Of course, in this case, you have to be absolutely, positively 100 % sure that col1 and col2 will never have a value of ‘impossible value’ in them. Moreover, instead of NVL, you can also use similar functions.

Sources and Resources:

• AskTom thread
• PSOUG Morgan’s Library

Filed in Oracle, Tips with 8 Comments | Tags: function, null, sql, undocumented

---

SELECT   last_name, salary,
         ROUND (salary / SUM (salary) OVER () * 100,
                2
               ) percent_of_total
    FROM employees
   WHERE job_id = 'PU_CLERK'
ORDER BY last_name;

        LAST_NAME           SALARY              PERCENT_OF_TOTAL            
------------------------- ---------- -------------------------------------- 
Baida                           2900                                  20.86 
Colmenares                      2500                                  17.99 
Himuro                          2600                                  18.71 
Khoo                            3100                                   22.3 
Tobias                          2800                                  20.14 

5 row(s) retrieved

SELECT   last_name, salary,
         ROUND
            (ratio_to_report (salary) OVER () * 100,
             2
            ) AS percent_of_total
    FROM employees
   WHERE job_id = 'PU_CLERK'
ORDER BY last_name

SELECT 'hi there' AS mycol
 FROM DUAL
 WHERE lnnvl (1 = 2) 

 MYCOL 
 ---------- 
 hi there