0

When I try to store KID numbers in SQL server I end up with scientific notation (6.9xxxxE+18). They are now stored in a FLOAT datatype. While I understand that this saves space, It really doesn't do much for the readability. I want them to be the following: 69xxxxxxxxxxxxxxxxx (numbers instead of x).

I've tried the following:

convert(bigint, [kid])
Arithmetic overflow error converting expression to data type bigint.

convert(varchar, [kid])
6.9xxxxE+18


str(convert(varchar, [kid]))
**********
B.Doe12
  • 23
  • 4
  • One option would be to handle this in your presentation layer. – Tim Biegeleisen Mar 06 '17 at 09:20
  • 2
    A KID is not a "number" in the normal sense. It is an identifier composed of numerical digits. You should store a KID as a varchar(x) field, not as a numeric field (of any kind or type). – Lasse V. Karlsen Mar 06 '17 at 09:21
  • The number length you've shown with that `69xxx...` is too long to be stored accurately in a float. Try this: `DECLARE @X FLOAT (newline) SET @X = 6912345678901234567 (newline) SELECT @X - 6912345678901234566` in SQL Server (notice the last digit is different) and observe that the result is 0. These numbers are not accurately stored, **you're losing information**. – Lasse V. Karlsen Mar 06 '17 at 09:27
  • As Lasse says, although they're composed of decimal digits, they're not really numbers - you wouldn't try to *add two together* or *multiply* them, or perform any other maths operation upon them. As such, they should be stored as text, not as a number. – Damien_The_Unbeliever Mar 06 '17 at 09:36

0 Answers0