msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

SQL Function Spotlight: TRIM

The SQL function TRIM has been around since Oracle 8i and maybe earlier. TRIM enables you to trim characters from a character string. The following examples demonstrate its usage and show you a few little known features of this simple function.

Remove leading and trailing blank spaces:

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

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

Remove any leading characters equal to ‘x':

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

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

Remove any trailing characters equal to ‘x':

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

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

Removes leading and trailing characters equal to ‘x':

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

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

Removes leading and trailing characters equal to ‘x’ (Same as BOTH):

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

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

My usage of this function has mostly been to trim blanks from both ends of a string. How about you?

Related functions: RTRIM and LTRIM.

6 Comments | Filed in Oracle | Tags: , ,


Previously Undocumented LNNVL SQL Function Buggy

LNNVL is a SQL function that takes a condition as an argument. It returns TRUE if the condition is FALSE or NULL. It returns FALSE if the condition is TRUE. It is available but not documented in Oracle database versions prior to 10gR1.

In his comment to my previous post about this function, Laurent demonstrated how LNNVL could generate the error ORA-03113: end-of-file on communication channel (tested on Oracle XE):

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

It looks like a bug to me.

4 Comments | Filed in Oracle, Tips | Tags: , , ,


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:

8 Comments | Filed in Oracle, Tips | Tags: , , ,


Cool Undocumented SQL Function: REVERSE

Let’s say that you want to check whether a word or a sentence reads the same backward or forward, how do you do it in SQL? you use the REVERSE function. We’ll see an example of that below, but first, a warning. Unlike LNNVL, REVERSE is still an undocumented SQL function, which means that it should be used with caution because “undocumented” also means “not supported”. With that warning out of the way, let’s explore this undocumented function and find out what other documented alternatives exist that have the same functionality as REVERSE.

REVERSE has been around since 8i, maybe even prior to 8i. I have tested it on 8.1.7 and it worked. Since REVERSE is undocumented, the best way to know what it does is to try it out. So, let’s go through a few examples: Continue reading…

6 Comments | Filed in Oracle | Tags: , ,


Cool SQL Analytic Function: RATIO_TO_REPORT

RATIO_TO_REPORT is a very handy and useful function. Before going through what it does, let’s first look at this example:

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;

Returns:

        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

The percent_of_total column represents the salary for each employee as a percentage of the total salary paid to all clerks. The above query can also be re-written using the RATIO_TO_REPORT SQL function:

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

Both queries return the same output.

Oracle documentation definition: The RATIO_TO_REPORT function computes the ratio of a value to the sum of a set of values. Its syntax is:

RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )

In this, the following applies:

  • expr can be any valid expression involving column references or aggregates.

  • The PARTITION BY clause defines the groups on which the RATIO_TO_REPORT function is to be computed. If the PARTITION BY clause is absent, then the function is computed over the whole query result set.

Finally, Tom Kyte used the RATIO_TO_REPORT function to calculate the percentage of each browser hitting his blog and to calculate the percentage of his posts per month. Sergio Leunissen used the RATIO_TO_REPORT function as a basis for an APEX (HTML DB) report.

SQL Analytic functions are simple and powerful.

4 Comments | Filed in Oracle, Tips | Tags: , ,


Undocumented LNNVL SQL function now safe to use

Connect to your Oracle database and try the following:

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

You should get :

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

Now, you ask, what is LNNVL? You search the Oracle documentation, but you cannot find any mention of it in releases prior to 10gR1, yet you successfully execute the above query in your Oracle 9.2 (or even 8.1.7) database!

LNNVL had been an undocumented SQL function until Oracle database version 10gR1. Let’s go back in time a bit and read what Jonathan Lewis and Lex de Haan wrote about it:

From: Jonathan Lewis
Date: Sun, Nov 2 2003 10:18 am
To: comp.databases.oracle.server
Message:

lnnvl(predicate) is true if predicate is false or null. I can’t say "for sure", but I believe it appeared somewhere around 7.3, or maybe 7.2 to handle problems arising from the SQL generated by a query co-ordinator for its parallel query slaves when Oracle was trying to get partition elimination on partition views. (And if it really is there for PQ problems, I’d second Richard’s advice – don’t use it, you never know when it’s going to disappear).

 

From: Lex de Haan
Date: Sat, 29 May 2004 21:27:53
To: oracle-l
Message:

The ANSI/ISO SQL standard offers the three operators you need: "IS TRUE", "IS FALSE", and "IS UNKNOWN". But as far as I know, no vendor has implemented these. Note the power of these operators; they accept a Boolean argument. The "IS NULL" becomes very clumsy as soon as multiple predicates are involved. One nice Oracle function (that has been around for quite a while, but was undocumented until 10g) is the LNNVL function.

The Oracle’s LNNVL function:

  • Is similar to the SQL/Foundation:2003 standard’s IS NOT TRUE (or IS FALSE).
  • Is not documented in Oracle database versions prior to 10gR1.
  • Provides a concise way to evaluate a condition when one or both operands of the condition may be null.
  • Can be used only in the WHERE clause of a query.
  • Takes a condition as an argument. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.
  • Returns TRUE if the condition is FALSE or UNKNOWN (NULL).
  • Returns FALSE if the condition is TRUE.
  • Is sometimes used internally by the Oracle Database to rewrite queries.

So, if you’re stuck with Oracle 8i or 9i, you can safely use the undocumented LNNVL function, knowing that it has become documented and supported in 10g and above.

Related Oracle documentation.

6 Comments | Filed in Oracle | Tags: , ,


The Q-quote mechanism

In Oracle, you quote string literals in single quotes. If you have single quotes inside that string literal, you have to escape each single quote with another single quote. But, did you know that, in 10gR2, there is an alternative approach, called the “Q-quote mechanism”?. I only knew about it when Martin mentioned it on OraQA. Continue reading…

11 Comments | Filed in Oracle, Tips | Tags: ,


Forgotten SQL Function: LEAST

At least I forgot all about LEAST, a very simple Oracle SQL function.

You want to select the smallest value from a list of expressions, you use LEAST:

LEAST(expr [, expr ]…)

Some examples: Continue reading…

Comments Off | Filed in Oracle | Tags: ,


Keep first, Keep last

A famous person once said: Oracle’s analytic SQL functions rock, Oracle’s analytic SQL functions roll. I totally agree. However, I would like to add to this that they rock and roll on a much faster beat if you know more about them. To explain what I mean, let’s take an example:

Continue reading…

2 Comments | Filed in Oracle, Tips | Tags: , ,


NVL, NVL2 or COALESCE?

In Oracle, as in other RDBMSs, nulls should be given a special treatment. In Oracle, there are three (maybe more?) functions that deal with nulls, NVL, NVL2 and COALESCE. I would like to ask you a question, but first, let me quickly refresh my memory:

Continue reading…

12 Comments | Filed in Oracle | Tags: , ,