SQL Clone
SQLServerCentral is supported by Redgate
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 
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


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

Loading comments...