SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 String Of Numbers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lemondash
Posting Yak Master

United Kingdom
159 Posts

Posted - 01/16/2013 :  06:22:58  Show Profile  Click to see lemondash's MSN Messenger address  Reply with Quote
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

3322 Posts

Posted - 01/16/2013 :  06:27:06  Show Profile  Reply with Quote
You might try the following, but you may have lost some precision and so the strings would not be exact at least in some cases
SELECT 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)
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/16/2013 :  07:29:17  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4344 Posts

Posted - 01/16/2013 :  14:38:47  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/16/2013 :  15:49:29  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/16/2013 :  17:22:06  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4344 Posts

Posted - 01/16/2013 :  17:26:20  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/16/2013 :  18:39:58  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000