Changing a FLOAT column to VARCHAR

Question

Post reply

Changing a FLOAT column to VARCHAR

Ray K

SSC-Dedicated

Points: 31163
More actions
January 15, 2010 at 12:19 pm

#219378

Okay, I'm coming across a problem that's just frustrating me.
Here's my scenario: I imported an Excel sheet into a staging table which it created automatically. (I'm actually trying to get my data into a target table, but for some reason it won't do it.)
When it created the staging table, it set the ZIP (as in zip code) field to type FLOAT. It's supposed to be VARCHAR(10).
This is causing me problems, because I get this error when I try to update my target table:
Msg 232, Level 16, State 2, Line 1
Arithmetic overflow error for type varchar, value = 123020379.000000.
The statement has been terminated.
I've tried CAST(ZIP as varchar(10)) and CONVERT(varchar(10), ZIP). I also tried alter table [StagingTable] alter column Zip varchar(10). All of them have been to no avail. Every one is throwing the overflow error.
Ideas?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

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

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

Ray K SSC-Dedicated Points: 31163 More actions · Answer 1

Update: I came up with a kludge solution where I only updated a ZIP with len(ZIP) = 5.

For my future refence: any thoughts to my initial post?

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

LutzM SSC Guru Points: 107049 More actions · Answer 2

Since FLOAT is an approximate-number data type, the character length may vary, therewith exceeding the VARCHAR(10) limit.

You need to get the value converted to a "known length".

Either one of he following samples should work:

SELECT cast(cast(ZIP AS decimal(10,0)) AS varchar(10))

SELECT cast(cast(ZIP AS decimal(14,4)) AS varchar(14))

SELECT cast(cast(ZIP AS int) AS varchar(10))

Edit: to change your column I would add an "intermediate column" with the correct data type, insert the converted values from column ZIP, verify everything worked fine, drop column ZIP and rename "intermediate column" to ZIP.

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]

Ray K SSC-Dedicated Points: 31163 More actions · Answer 3

lmu92 (1/15/2010)
Since FLOAT is an approximate-number data type, the character length may vary, therewith exceeding the VARCHAR(10) limit.
You need to get the value converted to a "known length".
Either one of he following samples should work:
SELECT cast(cast(ZIP AS decimal(10,0)) AS varchar(10))
SELECT cast(cast(ZIP AS decimal(14,4)) AS varchar(14))
SELECT cast(cast(ZIP AS int) AS varchar(10))
Edit: to change your column I would add an "intermediate column" with the correct data type, insert the converted values from column ZIP, verify everything worked fine, drop column ZIP and rename "intermediate column" to ZIP.

Okay, yeah, that makes sense. Thanks.

Ultimately, I ended up dropping the staging table and getting rid of all the FLOAT declarations in my DTS. After several attempts, lots of tweaking, and a number of swear words, I finally got it to work.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 4

One thing about trying to cast or convert a Zip code to a decimal or int, you'll run into problems where a zip starts with a 0 as that will be dropped.

Shamless self promotion - read my blog http://sirsql.net

LutzM SSC Guru Points: 107049 More actions · Answer 5

Nicholas Cain (1/15/2010)
One thing about trying to cast or convert a Zip code to a decimal or int, you'll run into problems where a zip starts with a 0 as that will be dropped.

Fortunately, that's one of the easier problems to solve:

Either a number of '0' is added once it's converted into a varchar column for a values with len< 5 or the conversion needs to be changed to something like

SELECT RIGHT(100000 + CAST(ZIP AS INT), 5)

You might have noticed that I didn't have to do the conversion into char(5). Using the RIGHT function will force SQL Server to perform an implicit conversion from INT into a character data type.

However, your point is absolutely valid if the conversion is done from character to int. But in this case it's stored as FLOAT, meaning leading Zeros are already dropped...

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]

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

lmu92 (1/15/2010)
Since FLOAT is an approximate-number data type, the character length may vary, therewith exceeding the VARCHAR(10) limit.
You need to get the value converted to a "known length".
Either one of he following samples should work:
SELECT cast(cast(ZIP AS decimal(10,0)) AS varchar(10))
SELECT cast(cast(ZIP AS decimal(14,4)) AS varchar(14))
SELECT cast(cast(ZIP AS int) AS varchar(10))
Edit: to change your column I would add an "intermediate column" with the correct data type, insert the converted values from column ZIP, verify everything worked fine, drop column ZIP and rename "intermediate column" to ZIP.

Heh... and none of that will take care of a zip code like 02819. 😉

--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)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

Heh on me.... I've REALLY got to get into the habit of reading all the posts on a thread before I respond. :blush:

--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)