How to convert char to real to numeric

Question

How to convert char to real to numeric

sql_monkey

SSC Eights!

Points: 867
More actions
March 27, 2008 at 3:40 pm

#187905

Someone has written a sproc that outputs a CHAR(22) value, called a ControlNumber, that is the result of concatenating several integers together (the first and last are NEVER zero and no arithmetic operations are being performed). This CHAR(22) value is being stored as a REAL data type in a table.
My challenge is to join to this table using this ControlNumber. I have tried converting it to decimal or numeric using (22,0) or even (38,0) and get arithmetic overflow errors. I even tried to convert it back to char(22), but only get the abbreviated scientific notation.
Can anyone suggest a way to recover the original 22 digit value from the REAL data type?

Viewing 9 posts - 1 through 8 (of 8 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Kevin Durham (3/27/2008)
Someone has written a sproc that outputs a CHAR(22) value, called a ControlNumber, that is the result of concatenating several integers together (the first and last are NEVER zero and no arithmetic operations are being performed). This CHAR(22) value is being stored as a REAL data type in a table.
My challenge is to join to this table using this ControlNumber. I have tried converting it to decimal or numeric using (22,0) or even (38,0) and get arithmetic overflow errors. I even tried to convert it back to char(22), but only get the abbreviated scientific notation.
Can anyone suggest a way to recover the original 22 digit value from the REAL data type?

If you are really storing a 22-digit string as a REAL, the 15 of those digits are long gone.

The REAL data type has a precision of only 7 bytes. Even the FLOAT data type can only store 15 digits accurately, and BIGINT won't get you more than 19 digits. What you should be using is a NUMERIC(22) or larger.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

If it's for a join, as you say... why are you concerned with converting it back to a number? Just join to it...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql_monkey SSC Eights! Points: 867 More actions · Answer 3

Thanks rbarryyoung,

You confirmed what I feared was true from what I had read elsewhere. If REAL only holds 7 digits, and the other 15 are lost, I have a follow-up question.

To try and understand, I converted the CHAR to REAL, back to CHAR, and also from REAL to NUMERIC and got the results that follow:

DECLARE @Char CHAR(22), @real REAL, @Numeric NUMERIC(22,0)

SET @Char = '1000000000000000000001'

SET @real = CONVERT(REAL, @Char)

SET @Char = CONVERT(CHAR(22), @real)

SET @Numeric = CONVERT(NUMERIC(22,0),@Real)

SELECT @Char, @real, @Numeric

Result:

1e+021 1E+21 1000000020040877300000

in the last number above...where did the numbers after 7 digits come from ?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Kevin Durham (3/28/2008)
Thanks rbarryyoung,
You confirmed what I feared was true from what I had read elsewhere. If REAL only holds 7 digits, and the other 15 are lost, I have a follow-up question.
To try and understand, I converted the CHAR to REAL, back to CHAR, and also from REAL to NUMERIC and got the results that follow:
DECLARE @Char CHAR(22), @real REAL, @Numeric NUMERIC(22,0)
SET @Char = '1000000000000000000001'
SET @real = CONVERT(REAL, @Char)
SET @Char = CONVERT(CHAR(22), @real)
SET @Numeric = CONVERT(NUMERIC(22,0),@Real)
SELECT @Char, @real, @Numeric
Result:
1e+021 1E+21 1000000020040877300000
in the last number above...where did the numbers after 7 digits come from ?

Heh... that's how computers empty the "bit bucket" nowadays 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 5

Kevin Durham (3/28/2008)
Thanks rbarryyoung,
You confirmed what I feared was true from what I had read elsewhere. If REAL only holds 7 digits, and the other 15 are lost, I have a follow-up question.
To try and understand, I converted the CHAR to REAL, back to CHAR, and also from REAL to NUMERIC and got the results that follow:
DECLARE @Char CHAR(22), @real REAL, @Numeric NUMERIC(22,0)
SET @Char = '1000000000000000000001'
SET @real = CONVERT(REAL, @Char)
SET @Char = CONVERT(CHAR(22), @real)
SET @Numeric = CONVERT(NUMERIC(22,0),@Real)
SELECT @Char, @real, @Numeric
Result:
1e+021 1E+21 1000000020040877300000
in the last number above...where did the numbers after 7 digits come from ?

Remember the first pentium and "you will be approximated"? 😛

Seriously, float does not have an exact decimal value, so when scaled to higher precision, the error scales as well.

Create a program like this in your favorite programming language:

1. fill an array of single precision float numbers with random values

2. do a sum of the array

3. do a sum of the array as double and convert result to single

The difference is significant. Even if array is small (1000) and numbers too (1.52, 3.141593,...) to exclude overflows.

1E+21 is just decimal approximation of @real, if you convert it to float or numeric, you just see the approximation error.

Numeric is decimal, so it must be used whenever accuracy matters.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

Robert (3/28/2008)
Numeric is decimal, so it must be used whenever accuracy matters.

I was with you all the way up until you said that...

If you do cascaded conversions like this, what Robert spoke of is absolutely true... the changes in the inherent scale and precision will cause errors.

But decimal doesn't necessarily make things more accurate... float does, though...

DECLARE @Decimal DECIMAL(38,6)

DECLARE @float FLOAT

SET @Decimal = 1

SET @float = 1

SELECT @Decimal = @Decimal/3*3,

@float = @float/3*3

SELECT @Decimal, @float

The key is the scale and precision of things... if you change to DECIMAL(xx,7), the decimal answer will come out correctly.

How many digits are displayed on your calculator? 13? Did you know that internally, it calculates to at least 15 digits and only displays 13? Works the same way that float does... the math is done in binary (which is why things like 1/10 make wierd numbers in float) and then rounded for display. Why? Because decimal adders (Binary Coded Decimal or simply BCD adders) are bloody slow compared to binary adders and require more design/realestate/money on the chip because of the extra components necessary to make a BCD adder.

Use as much scale and precision as you can when doing calculations that involve anything other than simple addition and subtraction. Then, like a calculator, round the result for display. Using Decimal does not guarantee correct answers unless you have both the scale and precision correct and then you still need to round for display especially if you're doing money calculations (that's why the Money datatype has 4 decimal places instead of just 2).

If you take a look at some of the higher mathematical functions of SQL like SIN, COS, TAN, etc, the datatype returned is FLOAT. There's a reason for that. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 7

I knew it! 🙂

Well, with decimal the range matters, with float a lot less.

So, if you use calculations like this, you must use greater precision than 6.

DECIMAL(38,36) will give you correct result, though in this case DECIMAL(38,12) is enough.

Money data type is 8 byte integer, scaled by 10000, so it appears like decimal(19,4), but is faster in calculation and offers more optimal storage.

Float, integer, money are hardware native types, numeric is not.

Which type is best depends on particular case.

In money calculations you shouldn't use float.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

Robert (3/29/2008)
In money calculations you shouldn't use float.

Again... right with you until you said that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)