SQLTeam.com | Weblogs | Forums

Find numbers that won't fit into bigint field

tsql

#1

Hi,

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.

Thanks


#2

As a first try maybe find where there are non digits

I think.


#3

Doesn't return any rows


#4

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

SELECT * FROM YourTable
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

https://msdn.microsoft.com/en-us/library/ms187745.aspx


#5

I now get results:
08754654400416187200
08760009500264266700
05070009500120086500
91950023800002860450


#6

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);

#7

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:

SELECT *
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)


#8

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


#9

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

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