How do I filter this value

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  December 1, 2016 at 3:41 pm

  #319442

  The column submittedmetricqnty is a varchar(8) column.

  I get a error when I run the query . Why ? Because one row has a value '4,000.00'

  How can modify the below SQL to only grab numeric values ( THE ISNUMERIC function does not work here )

  Msg 8114, Level 16, State 5, Line 1

  Error converting data type varchar to numeric.

  Select

  CASE WHEN submittedmetricqnty NOT LIKE '%,%' THEN

  CAST ( submittedmetricqnty as DECIMAL(38,0 ) )*(1.0)

  ELSE

  NULL

  END

  from mhpdw2.transferdb.rx.rx4dclaims

• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  December 1, 2016 at 4:32 pm

  #1915596

  Are you sure that's the value that's actually throwing the error?

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  December 1, 2016 at 5:02 pm

  #1915600

  ZZartin: Found the error. There was a value '4,000.00'

  I managed to take care of that by doing a CAST( (CAST ( COL as MONEY )) as DECIMAL(38,0) )

  That works! I am all set!