Problems to UPDATE Float Field to NVARCHAR Field....Help me, please

Question

Problems to UPDATE Float Field to NVARCHAR Field....Help me, please

sanmon_11

SSChasing Mays

Points: 651
More actions
March 2, 2012 at 4:18 pm

#265384

Hi, I need data copy of one 'Float' Field to 'NVARCHAR' Field on the same Table, example:
update tbl_MotorEns set MotorEns_NoSerieS = MotorEns_NoSerie
the update successfully, but the records are wrong.
explain
MotorEns_NoSerie======>MotorEns_NoSerieS
1996124110868 ======>1.99612e+012
I dont now put some signs like '.' '+' 'e' and dosn't pass the number correctly.
Also try change the Data Type to the Field(MotorEns_NoSerie) to NVARCHAR, but show the same records.
Please, Could you help me?, please

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

There are several questions:

The most important one: why do you need to change a numeric value to a character value? What changed in the business case?

Why is the data type currently float? What values are stored in that column? (from your sample it looks like those are only numeric values)

You're already faced with one of the side effects of storing numeric values using a character data type: the value 1.99612e+012 is a valied number. The reason for being different than your original values is due to the internal rounding of a float value. Depending on the values it might be a better idea to convert it to NUMERIC(p,s) instead of varchar...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sanmon_11 SSChasing Mays Points: 651 More actions · Answer 2

1, 2 ... because we need to also accept alphanumeric intervals,

example: BD123456789 (serial number)

3 .... MotorEns_NoSerie field (serial number) was declared FLOAT since I think the table (I did not).

4 ..... At the moment stores only numeric data (serial number), but also needs to accept alphanumeric values??.

TESTS ...... I did was declare a varchar field so I accept alphanumeric values??, but it converts numeric values ??to letters ..... I also made a varchar field and I did an Update to the field new original field (as show in the previous post) and I get the same results.

Question:

Do you think that if I turn the field type float (MotorEns_NoSerie) to numeric and then do an Update to the field varchar (MotorEns_NoSerieS), respects the original values ??are not earmarked.

If your answer is no, then how I can do?

Thank so much for answering!!

LutzM SSC Guru Points: 107049 More actions · Answer 3

You could try CAST(CAST(YourColumn AS NUMERIC(x,0)) AS VARCHAR(x)), where X is the number of digits you need to store. This under the assumption that you don't have serial numbers with decimal places... ;-))

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sanmon_11 SSChasing Mays Points: 651 More actions · Answer 4

ok,

Do I have to do this in the SP where the MotoEns_NoSerie field does Insert?

or

in the part where I do the Update field 'Float' Field to 'NVARCHAR' Field?

thank so much for you help!!:hehe:

sanmon_11 SSChasing Mays Points: 651 More actions · Answer 5

Hi Lutz,

In the Query:

SELECT CAST(CAST(MotorEns_NoSerie AS NUMERIC(13,0)) AS VARCHAR(13)) FROM tbl_MotorEns

I got this Error:

Msg 8115, Level 16, State 6, Line 1

Arithmetic overflow error converting float to data type numeric.

any Idea??

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 6

sanmon_11 (3/5/2012)
Hi Lutz,
In the Query:
SELECT CAST(CAST(MotorEns_NoSerie AS NUMERIC(13,0)) AS VARCHAR(13)) FROM tbl_MotorEns
I got this Error:
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.
any Idea??
Thanks

What's the min and max number in the MotorEns_NoSerie column?

I guessed a length of 13 digits based on your previous posts. But there can be larger values. You might want to try NUMERIC(18,0) or even NUMERIC(38,0)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sanmon_11 SSChasing Mays Points: 651 More actions · Answer 7

Ey Luts,

and found the solution, I was able to pass data from field to field Varchar Float.

With this:

update tbl_MotorEns set MotorEns_NoSerieV = CAST(STR(MotorEns_NoSerie, 16, 0) AS VARCHAR(16))

and respect the records without changing anything.

thanks for your help and time.

Regards 🙂