Thanks everyone. It worked. I have applied your logic as below and it worked fine. CASE WHEN (charindex('H',D1_VAL)>0) and (charindex('M',D1_VAL)<1)

THEN CONVERT(FLOAT, SUBSTRING(D1_VAL, 0, charindex('H',D1_VAL))) *...