| Author |
 Topic  |
|
|
lemondash
Posting Yak Master
United Kingdom
156 Posts |
 Posted - 01/16/2013 : 06:22:58
|
Good Morning All,
I had a coloumn of data that contains a lomg string of numbers example '12477791777083' its been imported in to a colunm with a data type float. But i need to move this data to another table and a colunm that has a data type of varchar(50).
I'm try lots of casts and converts but not having much luck at the moment, can anybody help at all
This is what i have done so far but the ouput is '12477800000000'
DECLARE @CardNo FLOAT
SET @CardNo = 12477791777083
SET @CardNo = CONVERT(BIGINT, @CardNo)
SET @CardNo = CAST(@CardNo AS NVARCHAR(255))
SELECT @CardNo
|
|
|
James K
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 01/16/2013 : 06:27:06
|
You might try the following, but you may have lost some precision and so the strings would not be exact at least in some casesSELECT CAST(CAST('12477800000000' AS DECIMAL(38,0)) AS VARCHAR(50));Ideally, I would suggest that you reimport the string into a VARCHAR(50) column (or VARCHAR(100) or whatever length is required) |
 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 01/16/2013 : 07:29:17
|
I hope the smart people chime in on this one. No solution here really, just observations
DECLARE @CardNo FLOAT
DECLARE @FloatXX float(53) = 12477791777083
SET @CardNo = 12477791777083 -- 12,477,791,777,083
SET @CardNo = CAST(@CardNo AS NVARCHAR(255))
SELECT @CardNo as CardNo
,@FloatXX as FloatXX
,CONVERT(bigint,@Floatxx) as FloatXXBigInt
,CONVERT(varchar(255),@FloatXX) as FloatXXvar
,CONVERT(varchar(255),convert(bigint,@FloatXX)) as FloatXXbigVarc
Jim
Everyday I learn something that somebody else already knew |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3822 Posts |
Posted - 01/16/2013 : 14:38:47
|
I believe it has to do with the Default behavior of the CAST/CONVERT functions. From BOL: quote:
float and real Styles
When expression is float or real, style can be one of the values shown in the following table. Other values are processed as 0.
Value
Output
0 (default)
A maximum of 6 digits. Use in scientific notation, when appropriate.
1
Always 8 digits. Always use in scientific notation.
2
Always 16 digits. Always use in scientific notation.
126, 128, 129
Included for legacy reasons and might be deprecated in a future release.
So, if you use CONVERT with a STYLE of 2 it'll work:DECLARE @CardNo FLOAT
DECLARE @FloatXX float(53) = 12477791777083
SET @CardNo = 12477791777083 -- 12,477,791,777,083
SET @CardNo = CONVERT(NVARCHAR(255), @CardNo, 2)
SELECT @CardNo as CardNo
,@FloatXX as FloatXX
,CONVERT(bigint,@Floatxx) as FloatXXBigInt
,CONVERT(varchar(255),@FloatXX) as FloatXXvar
,CONVERT(varchar(255),convert(bigint,@FloatXX)) as FloatXXbigVarc
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 01/16/2013 : 15:49:29
|
I think the truncation of the value happened during your import.
As you can see from the example below, you should be able get all of the digits from a float column.
select
CardNoFloat,
CardNoBigInt = convert(bigint, CardNoFloat),
CardNoVarchar = convert(varchar(30),convert(bigint, CardNoFloat))
from
( -- Test Data
select CardNoFloat = convert(float(53),'12477791777083')
) a
Results:
CardNoFloat CardNoBigInt CardNoVarchar
---------------------- -------------------- ------------------------------
12477791777083 12477791777083 12477791777083
CODO ERGO SUM |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 01/16/2013 : 17:22:06
|
Interesting. When I run my query and Lamprey's I get
CardNo FloatXX FloatXXBigInt FloatXXvar FloatXXbigVarc
12477800000000 12477791777083 12477791777083 1.24778e+013 12477791777083
Is this a display issue? The only difference in my declare @cardno and
and @FloatXX is the inclusion of the default precision.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3822 Posts |
Posted - 01/16/2013 : 17:26:20
|
Humm, I'm using SQL2012. Not sure if that'd matter. The only thing I changed from your original sample was teh SET @CardNo:
SET @CardNo = CONVERT(NVARCHAR(255), @CardNo, 2)
And I get the following output:
CardNo FloatXX FloatXXBigInt FloatXXvar FloatXXbigVarc
12477791777083 12477791777083 12477791777083 1.24778e+013 12477791777083 |
Edited by - Lamprey on 01/16/2013 17:28:07 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 01/16/2013 : 18:39:58
|
It's not SQL 2012 v 2008, which I am on. It's my copy paste error
DECLARE @CardNo FLOAT
DECLARE @FloatXX float(53) = 12477791777083
SET @CardNo = 12477791777083 -- 12,477,791,777,083
SET @CardNo = CONVERT(NVARCHAR(255), @CardNo, 2)
SELECT @CardNo as CardNo
,@FloatXX as FloatXX
,CONVERT(bigint,@Floatxx) as FloatXXBigInt
,CONVERT(varchar(255),@FloatXX) as FloatXXvar
,CONVERT(varchar(255),convert(bigint,@FloatXX)) as FloatXXbigVarc
SET @CardNo = CONVERT(NVARCHAR(255), @CardNo)
SET @CardNo = CONVERT(NVARCHAR(255), @CardNo, 2)
SELECT @CardNo as CardNo
,@FloatXX as FloatXX
,CONVERT(bigint,@Floatxx) as FloatXXBigInt
,CONVERT(varchar(255),@FloatXX) as FloatXXvar
,CONVERT(varchar(255),convert(bigint,@FloatXX)) as FloatXXbigVarc
When I eliminated the first set @cardno, everything worked. I swear I'm not this stupid!
Jim
Everyday I learn something that somebody else already knew |
|
|
| |
Topic |
|
|
|
String Of Numbers
