I'm sure this is familiar to anyone having various health codes that have a mixture of alphanumeric characters.
For the simplest ones (skipping the ones that begin with "V"), I *thought* I was ok when converting the '001 - 999.99' range that is currently stored as a VARCHAR into an INT with a series of CASE statements for each grouping of ranges (although I realize these are actually decimal values but I haven't figured out how to add precision and scale in an applicable way)
CASE WHEN CAST(mcd.LINE_DIAG1 AS INT) BETWEEN 001 AND 139.99 --convert STRING TO INTEGER FOR EASIER SEARCHING
THEN 'Infectious and Parasitic Diseases'
But some of the data is not converting and I'm getting errors such as:
"Conversion failed when converting the varchar value '250.00' to data type int"
Maybe someone has been in this situation and could offer suggestions?
Many thanks in advance!