CODEWITHSUNDEEP
sql-server
good-to-know
good-to-know

Reputation: 742

How to convert/cast varchar to float?

I just need to bulk update data. For this, I've created a table type and stored procedure.

I've created type with this:

CREATE TYPE [dbo].[ItemsUpdate] AS TABLE
(
  [ItemPartNumber] [varchar](100) NULL,
  [VendorName]     [varchar](100) NULL,
  [Price]          [varchar](100) NULL
)
GO

And my stored procedure be like this:

ALTER PROCEDURE [dbo].[UpdateItemsByVendorPrice]
   @tblItemUpdate ItemsUpdate READONLY
AS
BEGIN
  SET NOCOUNT ON;

  MERGE INTO Items orgTable
  USING @tblItemUpdate typeTable ON orgTable.ItemPartNumber = typeTable.ItemPartNumber

  WHEN MATCHED THEN
     UPDATE SET orgTable.Price = CONVERT(FLOAT, typeTable.Price);
END

Even though, I used convert(float,typeTable.Price), I got this error.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Error converting data type varchar to float

Upvotes: 0

Views: 1156

Answers (2)

HLGEM
HLGEM

Reputation: 96572

The error is pretty clear, you have information in that column that does not fit the datatype of the column it is being inserted into.

You need to clean your data before trying to insert it. Apparently you have things in the price column in the data that are not actual prices. Usually these involve values for things like 'Call for Price' or 'N/A' in the part number files I have processed.

How you handle this depends on your business rules. If it is OK to dump values that do not have real prices you just need to filter for that. If is is not ok to have prices that are not money, then you need to fail the import and send the database to the person providing it for a fix. You might also need to skip those records but send them back to the provider so they can fix for future imports. All these are business decisions that only your organization can answer. Once you know what the business decision is, we can help show you how to implement it.

As a side note, do not use merge for this, you are only doing updates, so just use an update statement. Plus merge has flaws, I don't usually recommend it for use in production data. - http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

One thing that especially bothers me is that you will be updating all matching parts not just teh ones that changed price. That is significantly more work than the server needs to do. An update without a where clause is usually a poor choice. Never plan to update receords that don't need to be updated. This is especially true of you have a large file. Why update a million records when you only have 3 that changed? But even with small files, it is good to get into the habit of doing this correctly, so that when you do update a large dataset, the technique is natural to you.

Upvotes: 0

Carl Bussema
Carl Bussema

Reputation: 1714

UPDATE SET orgTable.Price = convert(float,typeTable.Price); says you want to convert the contents of typeTable.Price to a float. Likely you have bad data, something that can't be cast to a float, such as "foo" or improperly-localized data like 234,457.

If you are using SQL 2012 or above, you can use TRY_CONVERT and setup a catch, like:

declare @myfloat float;
SELECT @myfloat = TRY_CONVERT(float, typeTable.Price)
IF @myfloat IS NULL then BEGIN
   PRINT 'Cast failed from '
   PRINT typeTable.Price
END
ELSE BEGIN
   PRINT 'cast OK'
   --continue update as normal
END

Note that TRY_CONVERT returns null or the converted value, so if you are working with data that could actually be null, you should filter that case explicitly before the TRY_CONVERT.

Upvotes: 1

Related Questions