sql server - How to identify long value using sql

Question

I need to check whether the values received in a file upload column has exponential or long values.

For example, if value is 5.02E+13 instead of numeric value - 50100434157080 then need to restrict it with a message saying format is incorrect.

For this I pass the upload content from frontend to backend in a temporary table and then get it checked if passed value has exponential value or numeric value.

Tried using T-SQL function isnumeric() but it didn't give me expected result. Any other function available?

Answer 1

Since you mention using isnumeric I assume you are using SQL Server, in which case you can try try_cast, for example

select case when Try_Cast(Column as bigint) is null then 'not integer' else 'integer' end
from table

You could also use like

select case when Column like '%e%' then 'exponent' else 'number' end
from table