Left padding a FLOAT type with zeroes

Question

Left padding a FLOAT type with zeroes

Arsh

SSCertifiable

Points: 6345
More actions
July 29, 2018 at 2:18 am

#407255

Dear Friends,
I'm in a situation where in I've to Left Pad a column of type Float with zeroesto a length of 8. I've to then compare this with a CHAR type column in anothertable, which is always 8 digits. I tried doing this with the command below Igot after a search but the issue is this works great if the type is INT.The problem with FLOAT is that , it works well till the value in the @myIntvariable is 5 ,55,555,5555 ,55555, 555555 ,but the moment I test with 7digits(i.e 5555555:) in the column it throws Arithmetic overflow error as below : Please suggest any other way to achieve I m trying to. Thank you inadvance.
DECLARE @myInt float = 5555555;
SELECT RIGHT(REPLICATE('0',8) + CAST(@myInt AS VARCHAR(8)),8)
Arithmetic overflow error for type varchar, value = 5555555.000000.

Thank You
Arshad

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

andycadley SSCertifiable Points: 5296 More actions · Answer 1

If you know the values will always be whole numbers, you could cast it to an int first, i.e:

DECLARE @myInt float = 5555555;

SELECT RIGHT(REPLICATE('0',8) + CAST(Cast(@myInt as int) AS VARCHAR(8)),8)

Alternately you could cast the char values in your table to FLOAT and then compare them the other way (but you'll need to be careful as exact comparisons may not give you the answers you want in that case)

frederico_fonseca SSCoach Points: 16012 More actions · Answer 2

perfectly normal - floats do not convert to varchar the way you expect.

DECLARE @myInt float = 5555555;
SELECT CAST(@myInt AS VARCHAR(12)), RIGHT(REPLICATE('0',8) + CAST(cast(@myInt as int) AS VARCHAR(8)),8)
output
5.55556e+006 -- 05555555

as you are comparing to a number from what I could gather first convert to int then to varchar and it gives you your desired output

Arsh SSCertifiable Points: 6345 More actions · Answer 3

andycadley - Sunday, July 29, 2018 4:26 AM
If you know the values will always be whole numbers, you could cast it to an int first, i.e:
DECLARE @myInt float = 5555555;SELECT RIGHT(REPLICATE('0',8) + CAST(Cast(@myInt as int) AS VARCHAR(8)),8)
Alternately you could cast the char values in your table to FLOAT and then compare them the other way (but you'll need to be careful as exact comparisons may not give you the answers you want in that case)

Hi Andy and Fred,
It works really well with double cast. Thank You

Arshad

GaryV Hall of Fame Points: 3307 More actions · Answer 4

Arsh - Sunday, July 29, 2018 7:45 AM
andycadley - Sunday, July 29, 2018 4:26 AM
If you know the values will always be whole numbers, you could cast it to an int first, i.e:
DECLARE @myInt float = 5555555;SELECT RIGHT(REPLICATE('0',8) + CAST(Cast(@myInt as int) AS VARCHAR(8)),8)
Alternately you could cast the char values in your table to FLOAT and then compare them the other way (but you'll need to be careful as exact comparisons may not give you the answers you want in that case)
Hi Andy and Fred,
It works really well with double cast. Thank You
Arshad

If you know it's going to be an integer, why don't you declare it as in integer?

The problem, as others have alluded to, is that casting a float doesn't give the results we might "expect" and it takes up more characters. Try this:

DECLARE @myInt float = 5555555; SELECT CAST(@myInt as varchar);

The answer is
5.55556e+006

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 5

Arsh - Sunday, July 29, 2018 2:18 AM

You don't seem to understand how floating-point data works. A floating-point number is made up of two parts; mantissa and an exponent, separated by a marker. It's usually a capital E, but Algol used a subscript 10, some languages could accept a lower case e. Some required a leading digit, somewhat allow a zero sum required a one, etc. it used to be that every vendor at his own version of floating-point. This meant that Univac and IBM were different back in days of early Fortran. Today, everyone uses the IEEE standards and if they're serious about floating-point they have a chip with the software on it in their machine.

The important characteristic of this data type is that it's not fixed length. Think of it as an abstraction that is not exact. In floating-point math, when you say "a = b" there is actually a plus or minus epsilon fudge factor that you don't see. Back in the 1960s when I was learning Fortran, we spent two weeks learning how to write arithmetic expressions with floating-point numbers because of these rounding problems.

I've been using SQL since the SQL-86 standards, and I've never found the need for floating-point. There's a subtle difference in the standards between DECIMAL(s,p) and NUMERIC (s,p), but either one has been quite usable for any commercial work I've done since then. If I have a scientific database, then I try to find specialized packages for it.

My guess is that you don't need floating-point, but some idiot thought it would be a good way to handle money or something (using floating-point for financial calculations is illegal).

Please post DDL and follow ANSI/ISO standards when asking for help.