15

I have a SQL Server database.

One field has values which are like 

 ID      VALUE
  1      NEGATIF
  2      11.4
  3      0.2
  4      A RH(+)
  5      -----
  6      >>>>>
  7      5.6<
  8      -13.9

I want to CONVERT VALUE field to decimal, of course convert-able fields.

  1. What kind of SQL statement can do this?

  2. How can I understand which value is raising error while converting?

PS: I think this can solve WHERE VALUE LIKE '[a-z]' but how can I add more filter like [-+ ()] ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
uzay95
  • 16,052
  • 31
  • 116
  • 182

1 Answers1

70

Plain ISNUMERIC is rubbish

  • Empty string, +, - and . are all valid
  • So is +. etc
  • 1e-3 is valid for float but not decimal (unless you CAST to float then to decimal)

For a particularly cryptic but failsafe solution, append e0 or .0e0 then use ISNUMERIC

SELECT
   ISNUMERIC(MyCOl + 'e0')   --decimal check,
   ISNUMERIC(MyCOl + '.0e0')  --integer check

So

SELECT
    ID, VALUE,
    CAST(
          CASE WHEN ISNUMERIC(VALUE + 'e0') = 1 THEN VALUE ELSE NULL END
          AS decimal(38, 10)
        ) AS ConvertedVALUE
FROM
    Mytable
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 6
    Just in case anybody else finds this neat trick - the integer check should actually add '.e0' not '.0e0'. Otherwise if the value you are testing is a single + or - character isnumeric() will still return true. – Peter Jun 22 '12 at 11:05
  • 1
    @Peter, you actually need something like this to handle the one character case: CAST(CASE WHEN LEN(Value) = 1 AND ISNUMERIC(VALUE + '.e0') = 1 THEN VALUE WHEN LEN(Value) > 1 AND ISNUMERIC(Value + '.0e0') = 1 THEN VALUE ELSE NULL END AS INT) – Esteban Brenes Jan 18 '13 at 21:00
  • @EstebanBrenes - good catch. I obviously wasn't paying enough attention. I think in my case I only cared if it was a number I didn't care if it was an integer or decimal. – Peter Jan 21 '13 at 08:18
  • @Peter +1 for `.e0` to exclude '+', '-', and '' – walterhuang May 24 '13 at 09:46