convert nvarchar to int

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  November 4, 2009 at 7:45 am

  #216440

  Hi All,

  i have values in a amount column as follows,

  30,000.00

  -74,000.00

  45,000.00

  It's data type is nvarchar.

  when i try to convert this into Int, it does not work.

  I used the replace to get rid of ','.

  and when i check the values using isnumeric, every record returns 1.

  Please help.

  Thanks,

• matt6288

  SSCertifiable

  Points: 5058

  More actions

  November 4, 2009 at 7:58 am

  #1074819

  Try converting to decimal and then to int. Or trimming off the .00 on the end of each string.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  November 4, 2009 at 8:06 am

  #1074825

  Converting first to decimal and then that decimal to integer for example:

  DECLARE @Numb AS VARCHAR(50)

  SET @Numb = '-74,000.00'

  SELECT CAST(CAST(REPLACE(@Numb,',','') AS DECIMAL(10,2)) AS INT)

  But be careful as:

  DECLARE @Numb AS VARCHAR(50)

  SET @Numb = '-74,000.99'

  SELECT CAST(CAST(REPLACE(@Numb,',','') AS DECIMAL(10,2)) AS INT)

  Truncates the input value, that is -74,000.00 and -74,000.99 both return an integer value of -74000

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  November 4, 2009 at 8:17 am

  #1074831

  Thanks All

  Converting it to decimal and to INT worked.