There is a blog post about #TSQL CAST Conversion Error… give me the row please! where the posted showed some methods to identify the data where convertion error happens. Here are other approaches to identify the problamatic data.
Consider the following set of data.
create table #t (data varchar(100))
insert into #t
select '798234' union all
select '798.95' union all
select 'r84' union all
select '0.784' union all
select '$129' union all
select '8734.9823.6673' union all
select '000' union all
select '1,200' union all
select '44$129' union all
select '#890.99' union all
select null
When you try to convert the datatype into money datatype
select cast(data as money) from #t
You will get this error
Msg 235, Level 16, State 0, Line 3
Cannot convert a char value to money. The char value has incorrect syntax.
But it will not show you the row where the convertion falis. The difficult part is identify
bad data and convert good data only.
Here are two ways to identify bad data
Method 1 :
Use regular expression select data from #t
where data like '%[^0-9.]%' or len(data)-len(replace(data,'.',''))>1
The above query finds out data where there is atleast a character not listed in [0-9] and a dot or have multiple dots. It returns the following data
data
---------------------
r84
$129
8734.9823.6673
1,200
44$129
#890.99
Note that it will also return numerals which are formatted. However this will be a good way to start identifying the bad data that cannot be converted to specific datatype.
Method 2 : Use Try_parse() function (SQL Server 2012 onwards)
select data from #t
where try_convert(money,data) is null
The new try_convert() function converts the good data successfully and returns NULL for bad data. See the result below. try_convert() take cares of formatted data too
data
-----------------------
r84
8734.9823.6673
44$129
#890.99
NULL
Note : Method 1 will consider formatted data as bad data. If you want to simulate what try_convert() does, you can add extra condtions in the WHERE clause based on how you define the formation.
You may also be interested to read this post Finding out problematic data in Bulk Insert data truncation error