Nvarchar to Float

Question

Post reply

Nvarchar to Float

sunny.tjk

SSCertifiable

Points: 5041
More actions
April 24, 2014 at 7:01 am

#290577

Hi all,
How do i insert values from the staging table with nvarchar column to the actual table with float datatype column?
Any help is appreciated!
Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

Like this:

declare @pi nvarchar(10) = '3.14159E0'

select cast(@pi as float)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You don't need explicit conversion. The insert will handle it implicitly.

CREATE TABLE #Testchar(

myvarchar nvarchar(10)

)

CREATE TABLE #Testfloat(

myfloat float

)

INSERT #Testchar VALUES('3.14159E0')

SELECT * FROM #Testchar

INSERT #Testfloat

SELECT myvarchar

FROM #Testchar

SELECT * FROM #Testfloat

GO

DROP TABLE #Testchar

DROP TABLE #Testfloat

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

gbritton1 SSCertifiable Points: 6522 More actions · Answer 3

Luis Cazares (4/24/2014)
You don't need explicit conversion. The insert will handle it implicitly.

Sure, but Explicit is better than Implicit. SQL Server sometimes does not give you the implicit conversion you think you should get.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

gbritton1 (4/24/2014)
Luis Cazares (4/24/2014)
You don't need explicit conversion. The insert will handle it implicitly.
Sure, but Explicit is better than Implicit. SQL Server sometimes does not give you the implicit conversion you think you should get.

I would agree with other types of data such as manipulating dates or some other types that need certain format. Could you show me what would be the problem with this conversion? On SQL 2012+ I would use TRY_CONVERT() or TRY_CAST(), but on 2008 they're not available.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

Here's a great article on the topic:

http://www.brentozar.com/archive/2012/07/identifying-correcting-sql-server-implicit-conversion/

I stopped relying on implicit conversion years ago. In fact, in all languages I write I try to adhere to the "Explicit is better than Implicit" principle. That way, I never have to wonder (or worry) about what the compiler is doing with my conversion code.

Another good article here: http://www.techrepublic.com/blog/10-things/10-reasons-to-explicitly-convert-sql-server-data-types

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

gbritton1 (4/24/2014)
Here's a great article on the topic:
http://www.brentozar.com/archive/2012/07/identifying-correcting-sql-server-implicit-conversion/
I stopped relying on implicit conversion years ago. In fact, in all languages I write I try to adhere to the "Explicit is better than Implicit" principle. That way, I never have to wonder (or worry) about what the compiler is doing with my conversion code.

That doesn't show the problem posted in here. In fact, it's talking about preventing conversion at all. When doing ETL work, that's not possible all the times.

There's a very interesting phrase in the article:

Think Like the Optimizer
To get the best performance from your queries, you need to understand how the query optimizer works, and think like it.

When you know how the SQL Server engine works, you can decide when implicit conversion is adequate and when explicit is needed.

Using Explicit all the time, is a good practice, but sometimes is not needed.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

gbritton1 (4/24/2014)
Another good article here: http://www.techrepublic.com/blog/10-things/10-reasons-to-explicitly-convert-sql-server-data-types

Number 9 and 10 are just wrong. Taking aside that number 9 suggest to make a query non-SARGable to "improve server statistics", both examples rely on implicit transactions.

WHERE CAST(SickLeaveHours AS char(3)) <> 0 will do an implicit conversion to int after the explicit conversion to char(3). I have no idea what the datatype is, but data truncation is possible and the query makes no sense as SickLeaveHours shouldn't be negative.

Substracting datetimes values is to implicit convert them to a numeric value to be able to use the substraction operator. DATEDIFF(dd, startdatetime, enddatetime) should be used instead.

So, be careful. You might think that you're using explicit conversion when you're actually using explicit and implicit at the same time.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Luis Cazares (4/24/2014)
gbritton1 (4/24/2014)
Another good article here: http://www.techrepublic.com/blog/10-things/10-reasons-to-explicitly-convert-sql-server-data-types
Number 9 and 10 are just wrong. Taking aside that number 9 suggest to make a query non-SARGable to "improve server statistics", both examples rely on implicit transactions.
WHERE CAST(SickLeaveHours AS char(3)) <> 0 will do an implicit conversion to int after the explicit conversion to char(3). I have no idea what the datatype is, but data truncation is possible and the query makes no sense as SickLeaveHours shouldn't be negative.
Substracting datetimes values is to implicit convert them to a numeric value to be able to use the substraction operator. DATEDIFF(dd, startdatetime, enddatetime) should be used instead.
So, be careful. You might think that you're using explicit conversion when you're actually using explicit and implicit at the same time.

#9 is laughably incorrect on so many levels. I think the funniest part is that the article is discussing avoiding implicit conversions and the example they created has one blatantly in there.

WHERE CAST(SickLeaveHours AS char(3)) <> 0

Here we created a nonSARGable predicate by casting the column but we also added an implicit conversion because now that our previous integer value has been cast a char(3) there is implicit conversion to cast the integer 0 to a char(3).

I won't even go into how ludicrous this statement is...

If a numeric search or sort is slow, try converting to a character data type to speed things up.

I do agree that using an explicit conversion is often the best approach. However for an insert it doesn't really make much difference. The implicit conversion is ALWAYS going to convert to the target datatype. There is no other choice. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Sean Lange (4/24/2014)

Here we created a nonSARGable predicate by casting the column but we also added an implicit conversion because now that our previous integer value has been cast a char(3) there is implicit conversion to cast the integer 0 to a char(3).

Sorry to correct you but the integer 0 won't be converted. It's the column which will be converted back to an integer due to data type precedence.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

gbritton1 (4/24/2014)

Here's a great article on the topic:

http://www.brentozar.com/archive/2012/07/identifying-correcting-sql-server-implicit-conversion/

I stopped relying on implicit conversion years ago. In fact, in all languages I write I try to adhere to the "Explicit is better than Implicit" principle. That way, I never have to wonder (or worry) about what the compiler is doing with my conversion code.

Another good article here: http://www.techrepublic.com/blog/10-things/10-reasons-to-explicitly-convert-sql-server-data-types%5B/quote%5D

I have to agree. I also don't agree with number 4. I wonder if the author is aware of the CEILING and FLOOR functions. I n the case of number 4, FLOOR would be the function I would use instead of CAST.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Luis Cazares (4/24/2014)

Sean Lange (4/24/2014)
Here we created a nonSARGable predicate by casting the column but we also added an implicit conversion because now that our previous integer value has been cast a char(3) there is implicit conversion to cast the integer 0 to a char(3).
Sorry to correct you but the integer 0 won't be converted. It's the column which will be converted back to an integer due to data type precedence.

Doh!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/