can I use a value like "1,203" in a calculation?

• kevinwinters

  Say Hey Kid

  Points: 696

  More actions

  July 15, 2009 at 9:15 pm

  #384997

  If I try to do a calculation on a field abd I get errors like "Error converting data type nvarchar to float" - I checked the field and found that several numbers have doublequotes around them like this: "1,203.75" - (this was imported data and the field became nvarchar)

  Any chance I can convert that text:"1,203.75" to a number like 1203.75 on the fly?

  The number of rows is 2 million and most of the rows have legitimate numbers in them

  Thank you

• arun.sas

  SSChampion

  Points: 11831

  More actions

  July 15, 2009 at 9:43 pm

  #1025455

  kevinwinters (7/15/2009)

  ---

  Any chance I can convert that text:"1,203.75" to a number like 1203.75 on the fly?

  Hi,

  Try this

  declare @abc nvarchar(20)

  select @abc = '1,234.56'

  select cast(replace(@ABC,',','') as numeric(18,2))

  RESULT

  1234.56

• VaibhavChoubey@SQL

  Ten Centuries

  Points: 1033

  More actions

  July 16, 2009 at 1:43 am

  #1025501

  You can Use this directly on your table:

  IF OBJECT_ID('tempdb..#temp') IS NOT NULL

  DROP TABLE #temp

  CREATE TABLE #temp (val nvarchar(20))

  INSERT INTO #temp VALUES ('1,234.94')

  ,('1,134.34')

  ,('1,11,134.69')

  ,('15,456.30')

  SELECT CAST(REPLACE(val,',','') AS numeric(18,2))

  FROM #temp

• LeeM

  Right there with Babe

  Points: 733

  More actions

  July 16, 2009 at 5:48 am

  #1025544

  You may want to consider using the code above with an update statement to fix the data in the table.