CODEWITHSUNDEEP
sqloracle-database
LCJ
LCJ

Reputation: 22652

“ORA-01722: invalid number” error even if there are no numbers involved in the query

I have following query

SELECT T1.col2
FROM T1
  LEFT OUTER JOIN T2 
  ON ',' + T2.col2 + ','  LIKE '%,' + T1.col1 + ',%'

Fiddle

There are no numbers involved in it. Still it is showing following error

ORA-01722: invalid number : SELECT T1.col2 FROM T1 LEFT OUTER JOIN T2 ON ',' + T2.col2 + ',' LIKE '%,' + T1.col1 + ',%'

How can we fix it?

REFERENCES

  1. Oracle faq - ORA-01722

Upvotes: 0

Views: 1753

Answers (2)

Andrew Brennan
Andrew Brennan

Reputation: 340

The + operator expects numbers as it's operands. If you give it strings then they are invalid numbers. So it is technically correct, the best kind of correct.

Welcome to Oracle. :D

Upvotes: 1

LCJ
LCJ

Reputation: 22652

I am new to Oracle and it was an incorrect syntax. We need to use || instead of +. The concatenation operator is different than SQL Server

  1. Concatenation Operator
  2. Joining tables with LIKE (SQL)

However, the message ORA-01722: invalid number seems to be misleading

Upvotes: 2

Related Questions