Search

Top 60 Oracle Blogs

Recent comments

Can you write a working SQL statement without using any whitespace?

I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.

The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:

SQL> select 1.x from dual;

         X
----------
         1

The column header gives a good hint what happened above. Oracle has treated the X as the column alias.

Let’s remove the “x” and see:

SQL> select 1. from dual;

        1.
----------
         1

Now all works as expected, “1.” is treated as number ( 1. = 1.0 )

But the question remains, how come Oracle splits up the string “1.x” and decides that the “x” is the column and “1.” is the number part – considering that there’s no whitespace between the 1. and x?

The answer is that apparently the string tokenizer used by Oracle SQL parser is smart enough that it doesn’t rely only on whitespace for recognizing token delimiters. It is also able to use character class analysis for understanding where a literal ends and the next token (like column alias) starts.

So, it is possible to write valid SQL statements without using any whitespace at all. For example: