String Of Numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

String Of Numbers

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2013-01-16 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 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 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)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-16 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-16 : 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)

7020 Posts

Posted - 2013-01-16 : 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-16 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-16 : 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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-16 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources