ISNUMERIC()

• Nico Verbaenen

  SSChasing Mays

  Points: 649

  More actions

  January 21, 2004 at 4:09 am

  #177841

  If I use the following statement: CASE WHEN ISNUMERIC(Field) = 1 THEN CAST(Field as Numeric) ELSE 0 END. So when the field has a numeric expression I convert it. Otherwise I put the value zero. This work as long as the field doesn't contain the values . , + and -. Because for these values the ISNUMERIC function also returns True. Does anyone know a work around in one statement? Without of course blowing the statement out of proportion.

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  January 21, 2004 at 5:30 am

  #490950

  CASE WHEN Field LIKE '%[^0-9]%' THEN 0 ELSE CAST(Field AS numeric) END

  
  
  --Jonathan