Conversion failed when converting the varchar value ‘U’ to data type int

I’m getting an “error converting varchar to int” for fields Q1_c and Q2_c.

Those fields are CHAR(1) and the values for those fields are either 1,2,3,4,5, or U, but it will not convert when there is a U, so I am trying to change the value of U to something else like 0. I have also tried CAST(a.q2_c as varchar(1)) = 'U' – but that doesn’t work either. Should I be using INT or VARCHAR()? I have also tried using CONVERT instead of CAST.

ALTER VIEW [dbo].[vCDCP_rptInfantImprove]
as
select distinct
         IND.tb_cdcp_individuals as Individuals_IndividualID 
       , A.[tb_cdcp_individuals] as ATQInfant_IndividualID
       , A.CreateDate as ATQInfant_CreateDate
       , CAST(a.q1_c as varchar(3)) as A_Q1
       , CAST(a.q2_c as varchar(3)) as A_Q2
        ---Infant Sum
       , Sum(isnull(cast(a.Q1_c as int),0)) + Sum(isnull(cast(a.Q2_c as int),0)) as InfantSum
        ---Change U to 0
       , case when CAST(a.q1_c as int) = 'U' then 0 else 0 end as Unknown1
       , case when CAST(a.q2_c as int) = 'U' then 0 else 0 end as Unknown2
from DBO.tb_cdcp_ATQInfant a
left join DBO.tb_cdcp_individuals ind on a.[tb_cdcp_individuals] = ind.[tb_cdcp_individuals]
where ind.agegroup_c = 'C' 
group by IND.tb_cdcp_individuals, A.tb_cdcp_individuals, A.CreateDate   
       ,     a.q1_c
       ,     a.q2_c
GO

Answer

You can go about this in a couple of different ways:

Since the fields are in character format, replace ‘u’ with ‘0’ and then convert it into int:

CAST(Replace(q1_c,'u','0') as int)

Or if you wanted to create a new calculated field you can directly create an integer field:

Case when q1_c='u' then 0 else CAST(q1_c as int) end as q1_c_calc

Hope this helps.