如何将带引号的浮点数插入数字字段?

I have a lengthy script that tries to insert float values with quotes (ie. '15.6') in float fields using T-SQL.

我有一个冗长的脚本，试图用引号插入浮点值。'15.6')在使用T-SQL的浮动字段中。

I have reasons to believe this script works in its developer machine, but because of the quotes it fails on my SQL Server 2008.

我有理由相信这个脚本可以在它的开发机器上工作，但是因为它在我的SQL Server 2008上失败了。

Is there any way to configure my server to ignore those quotes when the column is numeric? That would be much easier than editing the script.

当列是数值时，是否有任何方法可以配置我的服务器以忽略这些引用?这比编辑脚本要容易得多。

4 个解决方案

#1

It happens that the quoted values are automatically converted to numbers if required.

如果需要，引用的值将自动转换为数字。

I didn't happened to me before because my machine was configured with a different decimal separator than the developers machine.

我以前没有遇到过这种情况，因为我的机器配置了与开发人员机器不同的十进制分隔符。

Now that I changed the Regional settings in the control panel, everything works.

现在，我更改了控制面板中的区域设置，一切正常。

#2

No, the quotes means it isn't numeric: you'd have to change the script.

不，引号意味着它不是数字的:您必须修改脚本。

How does SQL Server know you really mean float?

SQL Server如何知道您真正的意思是浮点数?

I also doubt that it works on another SQL Server instance too.

我还怀疑它在另一个SQL Server实例上也能工作。

#3

I would suggest that you first check the revision levels between the two sql servers. The following worked just fine on two I tried; versions 10.0.2746 and 10.0.1600

我建议您首先检查两个sql服务器之间的修订级别。下面的方法在我试过的两种中效果很好;版本10.0.2746和10.0.1600

/****** Object:  Table [dbo].[TestTable]    Script Date: 11/03/2010 14:48:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
    [test] [float] NOT NULL
) ON [PRIMARY]


GO

insert into TestTable(test) values ('15.6')

#4

As gbn said, SQL Server sees anything in single quotes as a string.

正如gbn所说，SQL Server将单引号中的任何内容视为字符串。

An alternative (which will still involve changing the script but may be easier than removing the quotes) would be to perform a cast on it:

另一种选择(仍将涉及修改脚本，但可能比删除引号更容易)是对其执行强制转换:

CAST('12.5' AS FLOAT)

铸造(“12.5”浮动)

...will return a float value you can use as you like.

…将返回一个浮动值，您可以随意使用它。

#1