Oracle - Column NUMBER(38) in WHERE clause - data has comma

Question

I am trying to query a table with a where clause on a Column Data Type NUMBER(38) . Since it is a NUMBER (as shown in below format) This would not work

SELECT * FROM PAT WHERE ID='1,234'

So I converted the String using TO_NUMBER & NLS_NUMERIC_CHARACTERS

SELECT TO_NUMBER ('1,234', '9G999',
   'nls_numeric_characters = ''.,''') AS FROM Dual;

output: 1,234

But, I am not sure how to use this in a WHERE clause as I get:

INVALID IDENTIFIER

Answer 1

Don't use strings for numbers. Use a proper number constant:

select  * 
from PAT 
where ID = 1.234

Numbers must not be enclosed in single quotes. In SQL the decimal separator is a .

See the manual for more details: https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF00220

Answer 2

Possibly a subquery may work:

select  * from PAT where ID IN (Select TO_NUMBER ('1,234', '9G999',
   'nls_numeric_characters = ''.,''') as from Dual;