Todays problem was brought to you by a highly customizable system with poor user validation and a user who doesn’t know the difference between data and formatting. First the system. The supplier has created a highly user customizable system, they can for example create a web form with zero coding – 100% user config. The problem is, technically, it creates a mess. So your nice new form with a number of questions stores all its answers in a single table with the data going into a nvarchar(max) column – which is fine for free-text columns, but not so good for integer fields. This is especially a problem when you have a form that has drop-down options (luckily stored in a different table more efficiently) which generates an amount which the end user can overtype in order to moderate it up or down, which has zero validation.
The data is “stored” as numeric in the database so, for example, 1200.34, but is formatted as currency – so £1,200.34. The problem occurs when the user overtypes the amount, when they do, they overtype it, say as, 1201.34, but they don’t enter 1201.34. They enter £1201.34. Now this is a problem as when I load the data into the Data Mart, I store the data as a numeric(18,2), which means I need to cast it. This will of course fail if the user has overtyped it as it isn’t a numeric – which has historically happened. The way I resolved it was to strip out the £ sign using a replace then to add a ISNUMERIC statement as a fail safe.
However despite my failsafe it failed today – the problem was with ISNUMERIC – if you read the man, it says “ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($)”. What it doesn’t tell you is it also covers commas – so:
select cast(replace('£1,220', '£', '') as int)
will fail with
Conversion failed when converting the varchar value '1,220' to data type int.
this is despite ISNUMERIC returning 1 (ie valid)
select ISNUMERIC('1,220')
The fix is to replace (well remove) commas as well as the pound sign (£). Going forward, in SQL2012 Microsoft has introduced Try_Cast which might be another option.