Why is the plus sign (+) before taking a string value in Oracle?

Question

Well, this is perhaps the quirk of the Oracle parser.

The following query is executed. Note the + before the “Y” on the last line.

SELECT * FROM (SELECT 'Y' AS field FROM DUAL UNION ALL SELECT 'X' AS field FROM DUAL) t WHERE t.field = +'Y' 

Why does the Oracle parser accept this? For a second, I thought it was because of the old external join syntax, but in this syntax the + symbol is surrounded by parentheses.

This also works:

 select +'Y1' from dual; 

and this:

 select 'A' || + 'Y1' from dual; 

This works (oracle converts the string to a number):

 select -'1' from DUAL; 

but not that ([Error] Execution (223: 9): ORA-01722: invalid number):

 select -'A' from DUAL; 

I wonder why + can be used up to varchar2 value. The Arithmetic operators section does not specify specific rules that apply to string values.

Answer 1

The unary + operator is defined as an identifier. Table 4-1 "Priority for SQL statements" in About SQL statements .

also:

 select + date '2015-01-01' from dual; 

January 01 2015 00:00:00

---

Edited to add.

"Identity" is the return of the argument. For another example from another language, see the Clojure identity function. Wikipedia has a page for the "authentication feature" .