Hi all hopefully I can explain my problem correctly, this is my first post on DaniWeb.
I'm currently working on a legacy medical research database that uses a Varchar field to store a decimal number, this field can also be used to store text entered by a researcher on the paper form used to populate the database.
I'm currently producing reporting views on this database and I need to find a way to detect alpha characters in the variable and return null if a decimal number is not the only thing in the string.
I need to use this value to calculate minimum, maximum and average values by casting or converting it.
the following example works for null values and empty strings, I just need to test for any alpha chars before i allow cast to change the value to a decimal.
select case when (HbA1c_perc is NULL) or (HbA1c_perc = '') then NULL else cast(HbA1c_perc as decimal(8,2)) end as HbA1c_perc from clinical
thanks in advance for any help you can give me.