[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error converting data type varchar to numeric.

Posted on 2014-09-02
4
Medium Priority
?
581 Views
Last Modified: 2014-09-03
Trying to update a field from varchar.
I get the following
Error converting data type varchar to numeric.

What's wrong with this?     QUANITY -  HAS VARCHAR VALUES LIKE   100.00, 99 , -99.99
QUANTITY2 IS DECIMAL(18,9)

UPDATE ACCT SET QUANTITY2 = CAST(QUANTITY AS DECIMAL(18,9)) WHERE ISNUMERIC(QUANTITY) = 1
0
Comment
Question by:JElster
  • 2
4 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 total points
ID: 40299077
One of the values in QUANTITY is not convertable to numeric, either because it has a non-number, thousands separator, empty string, etc..
Declare @foo varchar(50) 

-- These work
SET @foo = '-99.99'
SELECT CAST(@foo as DECIMAL(18,9))

SET @foo = '100.00'
SELECT CAST(@foo as DECIMAL(18,9))

SET @foo = NULL
SELECT CAST(@foo as DECIMAL(18,9))

-- These don't work
SET @foo = ''
SELECT CAST(@foo as DECIMAL(18,9))

SET @foo = '3.45+E09'
SELECT CAST(@foo as DECIMAL(18,9))

SET @foo = '1,234.56'
SELECT CAST(@foo as DECIMAL(18,9))

SET @foo = 'banana'
SELECT CAST(@foo as DECIMAL(18,9))

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 40299707
I don't see the bad record .. Isnumeric returns 0
I have millions of records. Any ideas how I can find the bad 1?
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 40299806
Unfortunately, IsNumeric is not the most reliable function, since strings such as $1,234.56 returns a 1, as would a scientific notation number (1e44), a European format number (1.234,56), and a number of other variations that may not be handled correctly in your system.

The most reliable checks are manual, based on LIKE and counting legal characters such as the decimal point and minus sign to ensure that there are not too many in the field.  A layered approach will ensure that you don't do too much work.

The code below will identify all of the records where isNumeric = 1, then validate the values against additional checks, and will only update items that match the restrictions for a number.  Note that the code assumes a unique field, AccountID, for each ACCT record - change this to match your table.

;WITH NumRecs AS (
	SELECT AccountID
	FROM ACCT
	WHERE ISNUMERIC(QUANTITY) = 1
	)
UPDATE ACCT
SET QUANTITY2 = CAST(QUANTITY AS decimal(18,9))
FROM ACCT AS a
INNER JOIN NumRecs AS n
	ON a.AccountID = n.AccountID
WHERE QUANTITY NOT LIKE '%[^0-9.-]%'		-- has only numeric, decimal point, or minus sign characters in the string
	AND 
		CASE 
			WHEN LEN(QUANTITY) - LEN(REPLACE(QUANTITY, '.', ''))<=1	-- max of one decimal point
			AND LEN(QUANTITY) - LEN(REPLACE(QUANTITY, '-', '')) <=1		-- max of one minus sign
			AND CHARINDEX('-', QUANTITY) <=1										-- Minus sign first
				THEN 1
			ELSE 0
		END = 1

Open in new window

0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 1600 total points
ID: 40299809
To find the bad record(s), reverse the logical checks, like this:

;WITH NumRecs AS (
	SELECT AccountID
	FROM ACCT
	WHERE ISNUMERIC(QUANTITY) = 1
	)
SELECT *
FROM ACCT AS a
INNER JOIN NumRecs AS n
	ON a.AccountID = n.AccountID
WHERE NOT (QUANTITY NOT LIKE '%[^0-9.-]%')		-- has only numeric, decimal point, or minus sign characters in the string
	OR 
		CASE 
			WHEN LEN(QUANTITY) - LEN(REPLACE(QUANTITY, '.', ''))<=1	-- max of one decimal point
			AND LEN(QUANTITY) - LEN(REPLACE(QUANTITY, '-', '')) <=1		-- max of one minus sign
			AND CHARINDEX('-', QUANTITY) <=1										-- Minus sign first
				THEN 1
			ELSE 0
		END = 0

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question