Solved

How to convert float to char

Posted on 2014-01-09
4
637 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.
0
Comment
Question by:Castlewood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 5

Assisted Solution

by:dannygonzalez09
dannygonzalez09 earned 400 total points
ID: 39768556
try doing this

SELECT CONVERT(CHAR(20), CONVERT(INT,part_no))
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39768567
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.
0
 
LVL 13

Accepted Solution

by:
Ashok earned 100 total points
ID: 39768585
SELECT convert(char(20),convert(numeric(12),part_no)) FROM TABLE1

or

SELECT convert(char(20),convert(numeric(12,0),part_no)) FROM TABLE1
0
 

Author Closing Comment

by:Castlewood
ID: 39768907
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question