convert float to nvarchar - 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

convert float to nvarchar

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-02-15 : 13:34:47

I have a data type float with a value of 10000487930 that I'm trying to insert into a data type nvarchar and am getting the result of '1.00005e+010'. I've tried cast(field as nvarchar) however this is not working. What might fix this? I cannot change the insert table data type.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-15 : 13:41:40

Try i.e CONVERT(myFloatNumber,DECIMAL(20)):
CREATE TABLE #tmp1
(ID INT ,
value NVARCHAR(50)
)

DECLARE @floatIn FLOAT
SET @floatIn = 10000487930

INSERT INTO #tmp1 (ID,value ) VALUES(1,CONVERT(DECIMAL(20,0),@floatIn))

SELECT * FROM #tmp1

DROP TABLE #tmp1

Jack Vamvas
-------------------
http://www.sqlserver-dba.com

kgsnathan
Starting Member

1 Post

Posted - 2011-06-09 : 09:51:57

Thanks , it is really helpful.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-11 : 12:37:44

>> I have a data type FLOAT with a value of 10000487930 that I'm trying to insert into a data type nvarchar and am getting the result of '1.00005e+010'. I've tried CAST(field AS NVARCHAR) however this is not working. What might fix this? I cannot change the insert table data type. <<

Yes, of course. That is how a FLOAT looks as a string. I see you used the term "field" instead of "column"; that tell me a lot about your mindset. You do not understand that SQL is based on abstract data types, that a tiered architecture should be doing the display formatting in the front end.

You have re-invented 1950's COBOL programming in SQL. Do no use NVARCHAR -- this is digits and not Chinese Unicode. Cast the float to a know DECIMAL(s,p), then cast that to (ugh!) a display character string.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources