-3

I am trying this

UPDATE table
SET col =  CONVERT(int, col)

and i am getting

Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value '106/1' to data type int.

Siyual
  • 16,415
  • 8
  • 44
  • 58
Pranay Kumar
  • 78
  • 10
  • 1
    I don't know what's confusing about the error message. You have a value `106/1` in your table, which isn't an `int`... It literally says that in the error message. Check your data... – Siyual Dec 01 '16 at 14:53
  • 1
    Just curious. Are you expecting [106 divided by 1] or [106] or [NULL] ? – John Cappelletti Dec 01 '16 at 14:55
  • 1
    I gave you an answer on this the other day http://stackoverflow.com/questions/40870435/i-have-column-with-decimals-and-i-want-it-to-make-it-real-number-please-see-belo replace 'col' with the case statement I gave you and it'll work. – Rich Benner Dec 01 '16 at 15:07
  • i am doing vice versa it working . as error one values is failing to do – Pranay Kumar Dec 01 '16 at 15:11
  • @PranayKumar the easiest way to do this is to run an update to convert the data as per your previous question. Once that's done then run your update to convert the column to an int. – Rich Benner Dec 01 '16 at 15:23

2 Answers2

3

If 2012+, you can use TRY_CONVERT() ... invalid would return NULL

UPDATE table SET col = TRY_CONVERT(int, col)

If not 2012+

UPDATE table SET col = case when isnumeric(col+'.01')=0 then null else CONVERT(int,col) end

Edit (learned that isnumeric() trick last night by Shnugo)

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

Check the type of argument you are passing into the method , your method asks for an argument of Type Int but you are giving it as a varchar that's why its throwing exception. Try with giving an int once

Bhanu Chandra
  • 1