Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL server – ISNUMERIC and checking for valid numeric tyopes

During a large ETL process from  a staging table  , UPDATE was creating an error on a CONVERT.

Msg 245, Sev 16, State 1, Line 17 : Conversion failed when converting the nvarchar value '??u?  ? ? ?AA???????????W?????????a??????K???????????????????1217' to data type int. [SQLSTATE 22018]

 

It was necessary to create a step , where values could be checked , if they were a valid numeric type.  ISNUMERIC returns 1 if it’s a valid numeric data type ( int, bigint, smallint, tinyint, decimal,numeric,money,smallmoney,float,real)

 

SELECT  unique_id, created_by ,handler_id ,team_id 
FROM
dbo.stage_table WHERE  isnumeric(rtrim(created_by)) = 0
OR isnumeric(rtrim(handler_id)) = 0 
OR  (isnumeric(rtrim(team_id )) = 0 

See Also

Arithmetic overflow error and isdate sql

Comments

Leave a comment on the original post [www.sqlserver-dba.com, opens in a new window]

Loading comments...