Cast Varchar to decimal detecting alpha chars first

[normmy]

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.

:)

[sknake]

IsNumeric() should do the trick:

IF OBJECT_ID('tempdb..#Test', 'U') IS NOT NULL DROP TABLE #Test
Create Table #Test
(
  Value varchar(10)
)

Insert Into #Test (Value) Values ('a')
Insert Into #Test (Value) Values ('1')
Insert Into #Test (Value) Values ('ab')
Insert Into #Test (Value) Values ('a2')
Insert Into #Test (Value) Values ('azz')
Insert Into #Test (Value) Values ('a2.2')
Insert Into #Test (Value) Values ('65.2')
Insert Into #Test (Value) Values ('99.1')
Insert Into #Test (Value) Values ('65.2a')

Select Min(Cast(Value as money)) As [Min], Max(Cast(Value as money)) As [Max], Avg(Cast(Value as money)) as [Avg]
From #Test
Where IsNumeric(Value) = 1

[normmy]

Thanks for that!

[sknake]

You're welcome

Please mark this thread as solved if you found an answer to your question and good luck!