0

Why below query is giving output 3 instead of 12? I am concating two characters still why output is number?

select '1' + '2' from dual;
O. Jones
  • 103,626
  • 17
  • 118
  • 172
Intekhab Alam
  • 9
  • 3
  • 2
    Beware of implicit conversions! Use explicit conversions whenever a conversion is needed. Using + for addition, Oracle takes that you wish to add two numbers and thus converts the strings to numbers, which conversion may fail in case they can't be converted. For string concatenations use ||. And use the conversion functions to_number, to_date, to_char, to_timestamp with the appropriate format masks and eventually with default value on conversion error. – Bogdan Dincescu May 15 '23 at 11:16

1 Answers1

6

In Oracle, + is the addition operator and || is the string concatenation operator.

If you want to concatenate strings then use:

select '1' || '2' from dual;

Which outputs:

'1'||'2'
12

If you want to add two numbers then use:

select 1 + 2 from dual;

Which outputs:

1+2
3

If you use:

select '1' + '2' from dual;

Then it is implicitly converted to:

select TO_NUMBER('1') + TO_NUMBER('2') from dual;

As the addition operator accepts numeric operands (and not string operands).

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117
  • 2
    Correct and very well explained. I'd like to add that `+` meaning "add" and `||` meaning "concatenate" is not limited to Oracle, but this is how it is defined in the ISO SQL standard. – Thorsten Kettner May 15 '23 at 11:04