We help IT Professionals succeed at work.

How to convert float to char

795 Views
Last Modified: 2014-01-09
I imported an Excel table with only one column which is with Part_No of 8 digit numeric, such as 10101241, etc. After imported, the data type in SQL becomes float and I have a trouble to convert the data type from float to char. Here are the results I got:

SELECT part_no FROM tab
shows the exactly 8-digit part numbers listed but they are in float data type and I cannot do any joint using it even they look fine.

SELECT CONVERT(CHAR(20), part_no) results in 1.01002e+007 for the first record 10101241.

SELECT CONVERT(CHAR(8), part_no) results in the error:
"Arithmetic overflow error for type varchar, value=10100241.000000."

If I got into the table and change the column from float to CHAR(20), all part numbers become 1.01002e+007, etc.

What should I do? Please help.
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
I'd reimport but during the import modify the data mapping to change the default data type to an int if you want an it, or string if you need a string.
Sr. Software Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
So I need to convert the float to int before further converting to char.
Also I found there is no why to change the data type in the import wizard.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.