Error converting data type varchar to numeric

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 4, 2010 at 12:03 pm

  #238447

  Hi

  My Table is:

  CREATE TABLE CDN

  (

  [# CP Code] varchar(50),

  [Time] varchar(50),

  [Total Volume in MB] varchar(50)

  )

  INSERT INTO CDN

  SELECT '903','7/18/2010','224195.504' UNION ALL

  SELECT '903','7/19/2010','222747.2187' UNION ALL

  SELECT '903','7/20/2010','231171.2056' UNION ALL

  SELECT '903','7/21/2010','217138.7239' UNION ALL

  SELECT '903','7/22/2010','220184.8136' UNION ALL

  SELECT '903','7/23/2010','213464.8818' UNION ALL

  SELECT '903','7/24/2010','225835.5601' UNION ALL

  SELECT '1204','7/18/2010','357220.6254' UNION ALL

  SELECT '1204','7/19/2010','369738.3753' UNION ALL

  SELECT '1204','7/20/2010','350584.1343' UNION ALL

  SELECT '1204','7/21/2010','365990.1172' UNION ALL

  SELECT '1204','7/22/2010','366660.9649' UNION ALL

  SELECT '1204','7/23/2010','348032.4622' UNION ALL

  SELECT '1204','7/24/2010','364754.5849' UNION ALL

  SELECT '2015','7/18/2010','131121.4758' UNION ALL

  SELECT '2015','7/19/2010','131896.0879' UNION ALL

  SELECT '2015','7/20/2010','142174.8376' UNION ALL

  SELECT '2015','7/21/2010','139842.816' UNION ALL

  SELECT '2015','7/22/2010','132125.7374' UNION ALL

  SELECT '2015','7/23/2010','138561.1871' UNION ALL

  SELECT '2015','7/24/2010','132163.1733' UNION ALL

  SELECT '17190','7/18/2010','41.9916' UNION ALL

  SELECT '17190','7/19/2010','42.0314' UNION ALL

  SELECT '17190','7/20/2010','63.9657' UNION ALL

  SELECT '17190','7/21/2010','54.0577' UNION ALL

  SELECT '17190','7/22/2010','10.7518' UNION ALL

  SELECT '17190','7/23/2010','95.443' UNION ALL

  SELECT '17190','7/24/2010','165.1344' UNION ALL

  SELECT '17192','7/18/2010','5.44E+07' UNION ALL

  SELECT '17192','7/20/2010','5.70E+07' UNION ALL

  SELECT '17192','7/21/2010','5.88E+07' UNION ALL

  SELECT '17192','7/22/2010','5.92E+07' UNION ALL

  SELECT '17192','7/23/2010','8.29E+07' UNION ALL

  SELECT '17192','7/24/2010','6.68E+07'

  I have Created a View on this Table as:

  CREATE VIEW View_CDN

  AS

  SELECT

  CAST([# CP Code] AS CHAR(5)) AS [CP Code],

  CAST([Time] AS DATETIME) AS [Date],

  CAST([Total Volume in MB] AS Decimal(15,8)) AS [TotalVolumeinMB]

  FROM CDN

  GO

  But when I try to run:

  SELECT * FROM View_CDN

  GO

  It gives me the below error:

  Msg 8114, Level 16, State 5, Line 1

  Error converting data type varchar to numeric.

  I know the problem is with the [Total Volume in MB] Column. But how to correct this problem?

  Thanks

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 4, 2010 at 2:41 pm

  #1202382

  Hi

  Yes,working with CSV files is really hard. Lot of manual formating has to be done.

  SELECT CAST ('6.68E+07' AS FLOAT)

  Do I have to do this for every such value? I have a lot of these.

  How would this be included into my above query?

  Thanks

• divyanth

  SSCarpal Tunnel

  Points: 4303

  More actions

  August 4, 2010 at 2:55 pm

  #1202388

  you don't need to do that for all manually, use an update statement.. to update all values with 'E' in it using a case statement with Patindex

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 4, 2010 at 3:05 pm

  #1202392

  Hi

  Divyanth,Can you update my query with what you are saying ?

  Thanks

• divyanth

  SSCarpal Tunnel

  Points: 4303

  More actions

  August 5, 2010 at 7:44 am

  #1202743

  I would suggest using a SELECT that CELKO suggests as updating is always expensive..But if you still want to below is the code

  UPDATE TableName

  SET ColName = CAST (CAST(ColName AS FLOAT) AS DECIMAL(15,8))

  WHERE PATINDEX('%E%',ColName) <> 0

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 5, 2010 at 11:52 am

  #1203011

  Hi,

  Thankyou guys, great help.

  @celko .... I can't BULK INSERT from a CSV file into the table if I use the right datatypes. So I have to do all the manipulations in Views. I can only import data if I use VARCHAR. Also for now I don't want to use any ETL tools.

  @divyanth .... Thanks for the query .... but CELKO's query works for me as I don't want to change the table coz if I do, I would not be able to insert more data into the table from other CSV files.

  Thanks.