Convert Varchar to Float

Question

Convert Varchar to Float

cm62597

SSC Eights!

Points: 976
More actions
February 11, 2015 at 10:32 am

#297647

I have the following CASE statement:
SELECT CASE WHEN [TBL_whole_rock_geochem].au = 0 THEN '<' + [TBL_ActLab_codes].[Au] ELSE [TBL_whole_rock_geochem].[Au] END AS [Au det]
It errors on the highlighted code.
Error converting data type nvarchar to float
the code should return :
<0.5
<1
I have tried '<' + ISNUMERIC([TBL_ActLab_codes].[Au]) but received this error
Conversion failed when converting the varchar value '<' to data type int.
Does anyone know how I can concatenate the < with a float value?

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

The problem is that SQL server is trying to convert from varchar to float/int and you need to do it the other way (float to varchar).

Use this example:

SELECT CASE WHEN [TBL_whole_rock_geochem].au = 0 THEN '<' + CAST( [TBL_ActLab_codes].[Au] AS varchar(20)) ELSE CAST( [TBL_whole_rock_geochem].[Au] AS varchar(20)) END AS [Au det]

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

cm62597 SSC Eights! Points: 976 More actions · Answer 2

I have tried that as well you just get Error converting data type varchar to float.

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

I don't believe you. You're doing something else as my code should work without problems.

SELECT CASE WHEN [TBL_whole_rock_geochem].au = 0

THEN '<' + CAST( [TBL_ActLab_codes].[Au] AS varchar(20))

ELSE CAST( [TBL_whole_rock_geochem].[Au] AS varchar(20)) END AS [Au det]

FROM (SELECT CAST(342.156 as float) AS Au)TBL_ActLab_codes

CROSS

JOIN (SELECT CAST(3657.627 as float) AS Au

UNION ALL

SELECT CAST(0 as float) AS Au)TBL_whole_rock_geochem

Results from a CASE statement can have only one data type and to decide which is going to be used, the rules of data type precedence apply.

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

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 4

cm62597 (2/11/2015)
I have tried that as well you just get Error converting data type varchar to float.

Quick question, can you post the full query and the DDL for the relevant tables? Luis's code works perfectly for the snipped you posted but if it doesn't work at your end then you aren't telling us the full story.

😎

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

When you say this

SELECT CASE WHEN [TBL_whole_rock_geochem].au = 0

What is the data type of column [TBL_whole_rock_geochem].au column. because following can also generate the error. Consider the following example.

Declare @table Table ( cola nvarchar(20))

insert into @table

select '1' union all

select '.1' union all

select '0' union all

select ''

select * from @table

where cola = 0

Otherwise, Luis snippet should resolve your issue unless you are not telling the complete story.