Example:
This statement should only attempt to convert strings that can be converted to numbers
selectHowever it fails with the error above.
case
when isnumeric(StringValue) = 1 then (convert(numeric, StringValue))
else StringValue
end as ConvertStringValue
from myTable
Solution:
Isnumeric appears to evaluate an empty string as numeric, but convert fails attempting the actual conversion. To make the case statement work, add a condition to detect the empty string and do something with it (in this case make it null).
select
case
when StringValue = '' then null
when isnumeric(StringValue) = 1 then (convert(numeric, StringValue))
else StringValue
end as ConvertStringValue
from myTable
No comments:
Post a Comment