SQLTeam.com | Weblogs | Forums

Find numbers that won't fit into bigint field




I have a char field that I need to convert to bigint. I get an arithmetic overflow error. How can I find the values causing the problem? The table is over 200,000 rows.



As a first try maybe find where there are non digits

I think.


Doesn't return any rows


See if there are any numbers that are too large. BIGINT has 19 digits:

WHERE LEN(REPLACE(YourColumn,'-','')) > 19

Not all 19 digit numbers can be expressed as bigint, so if you still don't find anything, convert the string to float and see if it is greater than 2^63-1 or less than -2^63



I now get results:


The first 3 are ok because if you remove the leading zero, they are within the range of BIGINT. But the fourth one that starts with 9 has 20 digits and is too large to be stored as a bigint. That will give you an arithmetic overflow.

-- this will succeed.
SELECT CAST('08754654400416187200' AS BIGINT);

-- this will cause arithmetic overflow
SELECT CAST('91950023800002860450' AS BIGINT);


The largest bigint value allowed is 9,223,372,036,854,775,807 (19 digits). Thus, you can find values that are too large like this:

FROM table_name
WHERE CAST(char_field AS decimal(28, 0)) > 9223372036854775807

If you can have negative values, you'll need to add a check for that as well:

WHERE NOT (CAST(char_field AS decimal(28, 0)) BETWEEN -9223372036854775808 AND 9223372036854775807)


Guys thanks so much. Love this forum and always the same people helping me. Thank you :smile:


What math are you doing with something lie that? :open_mouth:

Also, look up what "field" means in SQL; you meant "column" instead.