Solved

How to convert float to char

Posted on 2014-01-09
4
603 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
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Analysis of table use 7 27
SQL index creation taking an abnormally long time 2 19
sql server query? 6 26
Azure SQL DB? 3 14
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now