How to remove blank rows from an input flat file?

Neeraj:

I have a short answer and a long answer.

Short answer: what version of SQL Server are you using? I don’t think -3.56443E-35 will fit in a DECIMAL(15,2). In versions prior to SQL Server 2016 (13.x), any float value less than 5E-18 that you tried to convert to such a DECIMAL would either throw an error, or round silently to zero, depending (AFAICR) on how you have the server or the connection configured. Later versions are smarter but you’d be better off reading the documentation than trusting in me.

Longer answer:
Unfortunately, many of today’s university programs are no longer teaching details of numeric representation in their classes for computer scientists or software engineers or developers. This is one of those cases where it can get us into trouble if we don’t understand the difference between common implementations of DECIMAL (or NUMERIC) and FLOAT / REAL / DOUBLE.

In this case, DECIMAL values in many implementations are stored as BCD values and computed long-form in software by Base-10 arithmetic. This is slower but prevents naively written mathematics from easily producing degenerate answers. In SQL Server, DECIMAL(15,2) means “store 15 digits total, and allow up to 2 of them to be to the right of the decimal point.” You could use this to store dollar amounts in the trillions. A number like 1,234,567,890,123.45 will fit in such a column or variable. A number like 0.12345 will be rounded to 0.12, and a number like 0.0000098765 won’t fit. (You’ll either get an error or it will round to zero, depending on a lot of different factors, i.e. MySQL will probably behave differently than Oracle.)

The implementation literally reserves enough space for 15 digits and decides where to assume the offset that the decimal takes.

Types such as FLOAT, REAL, or DOUBLE are floating point. They are good for navigation, graphics, and other mathematically intensive applications, but are not good for money. (If I have 10,925,379,656,943 Japanese Yen and you store it in a 7-significant-digit real, I can make 100,000 yen purchases over and over and over again and never affect my total.)

Floating point values are (on most 2’s complement processors) implemented as a binary mantissa and exponent pair. If I recall correctly, a SQL Server REAL has 24 bits of mantissa and 8 bits of exponent, which gives you roughly 7 significant digits in decimal, and a range of something like [+/-] 1.x E[+/-]38 to 3.y E[+/-]38 – you’ll have to look it up to find what x and y are, it’s not important to the point here. (What is important is that there’s a gap between the tiniest numbers and zero: this is where underflow happens.)

So, your value -3.56443E-35 might or might not fit in a SQL Server REAL, but it fits into a bigger type like FLOAT.

My big value 1,234,567,890,123.45 “fits” into a REAL, but not perfectly. It rounds to 1.234568E+12. If you convert the REAL back to a DECIMAL(15,2) you’d get 1,234,568,000,000.00, and the bank would be very angry you’d given me a free $109,876.55 if this was a banking application!

My very small value of 0.0000098765 does fit in a REAL, as 9.87650E-6. If you convert it to a DECIMAL(15,13), you’d get [0]0.00000987650 and all significant digits would be preserved.

I hope this long pedantic ramble has been of some use to either you or another reader, and hasn’t put you to sleep!! 😀