3

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.

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.

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.

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

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!

Patches
  • 1,115
  • 1
  • 10
  • 13
  • 4
    money is a fairly weird datatype, and I'd usually recommend against using it, even if you are dealing with currencies. I'd usually recommend a decimal type with an appropriate precision selected. (money has 4 decimal places, and performs rounding at each intermediate stage of a calculation) – Damien_The_Unbeliever Jan 06 '11 at 07:48
  • @Daimen_The_Unbeliever I'm stuck with it in the ultimate destination columns fields, I'm afraid. – Patches Jan 06 '11 at 15:45

1 Answers1

7

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

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

Edit:

Can do it in one line if you want

ISNUMERIC(REPLACE(REPLACE(@chkCOl, '$', ''), ',', '') + 'e0')
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 3
    money can accept currency symbols, so this may return 0 unfairly, e.g. `select ISNUMERIC('$4.10e0'),CONVERT(money,'$4.10')` – Damien_The_Unbeliever Jan 06 '11 at 07:43
  • Likewise thousands separators and other currency symbols. – onedaywhen Jan 06 '11 at 09:07
  • @Damien_The_Unbeliever, @onedaywhen: it depends on what the source data looks like. Also, money doesn't really support separators that are not $ and , – gbn Jan 06 '11 at 09:14
  • The '$' is a problem, it does have that. What if I passed the parameter to a function, did a REPLACE to remove '$' and ',', and then returned the result of that ISNUMERIC? – Patches Jan 06 '11 at 15:17
  • While money can accept commas and $, I would first clean the data of those things before using gbns check. You don't really want to store it that way anyway, so clean it. – HLGEM Jan 06 '11 at 15:53
  • @HLGEM check out the code I put in the edit of my post. I try to do that there. – Patches Jan 06 '11 at 16:30
  • @Patches: your function looks OK – gbn Jan 06 '11 at 16:31
  • @gbn It does! I just got done testing it. Thanks for your help! – Patches Jan 06 '11 at 17:02