在SQL Server 2008中，如何检查varchar参数是否可以转换为数据类型货币?

I've got a stored procedure I use to insert data from a csv. The data itself is a mix of types, some test, some dates, and some money fields. I need to guarantee that this data gets saved, even if it's formatted wrong, so, I'm saving them all to varchars. Later, once the data's been validated and checked off on, it will be moved to another table with proper datatypes.

我有一个用来插入csv数据的存储过程。数据本身是类型、测试、日期和货币字段的混合。我需要保证这个数据被保存，即使它的格式是错误的，所以，我将它们全部保存给varchars。稍后，一旦验证并签出数据，它将被移动到另一个具有适当数据类型的表。

When I do the insert into the first table, I'd like to do a check that sets a flag (bit column) on the row if it needs attention. For instance, if what should be a money number has letters in it, I need to flag that row and add the column name in an extra errormsg field I've got. I can then use that flag to find and highlight for the users in the interface the fields they need to edit.

当我在第一个表中插入时，如果需要注意，我想做一个检查，在行上设置一个标志(位列)。例如，如果一个货币数字中有字母，我需要标记该行，并在一个额外的errormsg字段中添加列名。然后，我可以使用该标志为用户在界面中查找和突出显示他们需要编辑的字段。

The date parameters seem to be easy, I can just use IF ISDATE(@mydate) = '0' to test if that parameter could be converted from varchar to datetime. But, I can't seem to find an ISMONEY(), or anything that's remotely equivalent.

日期参数看起来很简单，我可以使用IF ISDATE(@mydate) = '0'来测试该参数是否可以从varchar转换为datetime。但是，我似乎找不到ISMONEY()，或者任何与之相类似的东西。

Does anyone know what to call to test if the contents of a varchar can legitimately be converted to money?

有没有人知道，如果varchar的内容可以合法地转化为金钱，该打电话来测试?

EDIT: I haven't tested it yet, but what do you think of a function like this?:

编辑:我还没有测试过，但是你觉得这样的函数怎么样?

CREATE FUNCTION CheckIsMoney 
(
   @chkCol varchar(512)
)
RETURNS bit
AS
BEGIN
 -- Declare the return variable here
 DECLARE @retVal bit

SET @chkCol = REPLACE(@chkCol, '$', '');
SET @chkCol = REPLACE(@chkCol, ',', '');

IF (ISNUMERIC(@chkCOl + 'e0') = '1')
    SET @retVal = '1'
ELSE
    SET @retVal = '0'

RETURN @retVal

END
GO

Update

更新

Just finished testing the above code, and it works!

刚刚完成上述代码的测试，它就可以工作了!

1 个解决方案

#1

money is decimal in effect, so you test this way

货币实际上是十进制的，所以你可以这样测试

Don't use ISNUMERIC out of the box though: it's unreliable. Use this:

但是不要用“非数字的”:它不可靠。用这个:

ISNUMERIC(MyCOl + 'e0')

Note, if you have 6 decimal places then it will be lost on conversion to money

注意，如果你有6位小数，那么它将在转换成货币时丢失

Other question with more info why: How to determine the field value which can not convert to (decimal, float,int) in SQL Server

关于为什么的其他问题:如何确定SQL Server中不能转换为(十进制、浮点数、int)的字段值

Edit:

编辑:

Can do it in one line if you want

如果你愿意，可以在一行内完成吗

ISNUMERIC(REPLACE(REPLACE(@chkCOl, '$', ''), ',', '') + 'e0')

#1