SQL how to convert varchar to numeric without hanging on CAST error

I want to do the following:

UPDATE TempTable
SET Amount = CAST(SrcAmount AS numeric(12,2))

…but I get the error
Error converting data type varchar to numeric
on some value, but they all look valid.  This suggests that there’s an obscure, non-printing ASCII character hidden in one of the SrcAmount values.

Is there some way I can modify the SQL command above to ignore the CAST error and simply skip over those records which generate it?
tesupportAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
ISNUMERIC() has to support a very wide range of data types, and so it is not necessarily reliable for numeric/decimal data.  For example:
SELECT ISNUMERIC('1e1'),ISNUMERIC('$1')


Code below assumes you do not have a minus/negative sign in any value.  If you do, let me know, we can adjust the code for that.

UPDATE TempTable
 SET Amount = CAST(SrcAmount AS numeric(12,2))
WHERE LTRIM(RTRIM(SrcAmount)) NOT LIKE '%[^0-9.]%'
  AND LTRIM(RTRIM(SrcAmount)) NOT LIKE '%.%.%'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Run this to flush out any values that cannot be converted to a numeric data type, and see what is returned
SELECT SrcAmount 
FROM TempTable 
WHERE ISNUMERIC(SrcAmount) = 0

Open in new window


>This suggests that there’s an obscure, non-printing ASCII character hidden in one of the SrcAmount values.
Possibly.  In those situations I usually grab the source file, open in a super-dooper text editor like UltraEdit, ctrl-H to go into Hex mode, and find the ASCII code(s) of the offending character(s).  Then you can weed them out.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you're using SQL Server 2012/14, you can use TRY_CAST
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If not, then I would reverse Jim's suggestion :-)

UPDATE TempTable
SET Amount = CAST(SrcAmount AS numeric(12,2))
Where ISNUMERIC(SrcAmount) <> 0
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can be the decimal point. Which character are you using for decimal point? Dot or comma?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.