将数据类型varchar转换为float的错误。在sql server 2008中 [英] error converting data type varchar to float. in sql server 2008

问题描述

你好，

我有一个表测试，这个列名'observation_value_numerical'数据类型是varchar（10）。我想写查询

Hello ,
i have a table test in this a column name 'observation_value_numerical' datatype is varchar(10). I want to write query

SELECT top 4 CONVERT(float, observation_value_numerical),convert(float,'9.0'),* FROM LAB_OBSERVATION where cast (observation_value_numerical as float)>9

但它显示将数据类型varchar转换为float的错误。在sql server 2008



表结构是

observation_value_numerical

11.4

5.7

9.2

7.7



如何解决此错误





谢谢

Srishti

But it showing error converting data type varchar to float. in sql server 2008

Table structure is
observation_value_numerical
11.4
5.7
9.2
7.7

how to resolve this error


thanks
Srishti

推荐答案

最明显的是：不要存储它作为一个字符串。

表中某处有一个值，其中包含其他数字和小数点，或者服务器的语言环境设置为不使用'。'作为小数点分隔符的文化。



更改表格，以便将数据存储为浮点值：然后，所有令您不愉快的读取代码都会消失，问题也会消失。 />


永远不要将项目存储为字符串，除非它们是字符串：将日期存储为DateTime值，将数字存储为整数或浮点数。这可能看起来更多一些工作，但它会在以后节省很多麻烦 - 正如你开始在这里看到的那样。

The most obvious is: don't store it as a string at all.
Somewhere in your table there is a value which contains something other that digits and a decimal point, or the locale for the server is set to a culture that doesn't use '.' as a decimal separator.

Change the table so that you store the data as float values: then all you unpleasant-to-read code disappears, and the problem does as well.

Never store items as strings unless they are strings: store dates as DateTime values, store numbers as Integers, or floats. It may seem like a little more work, but it saves a lot of hassle an trouble later - as you are starting to see here.

试试这个：

Try this:

SELECT top 4 CONVERT(float, observation_value_numerical),
convert(float,'9.0'),* FROM LAB_OBSERVATION
where CONVERT(float, observation_value_numerical)> 9

使用

Use

CASE WHEN ISNUMERIC(observation_value_numerical) THEN CONVERT(float, observation_value_numerical)

要转换的
varchar to float。

to convert varchar to float.

这篇关于将数据类型varchar转换为float的错误。在sql server 2008中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！