Check this For better sql conditions

CREATE VIEW [dbo].[vw_irpts_members_import]
AS

Select
dbo.f_auto_eeid(Empl_id) as auto_eeid,
Case When dob is not null then
Case When ssn is Not Null then
case when Isnumeric(Rtrim(REPLACE(ssn,'-','')))=1 then
LEFT(Rtrim(REPLACE(ssn,'-','')),4)+REPLACE(CONVERT(VARCHAR(10), dob, 1), '/', '')
else
case When ssn4 is Not Null then
case when Isnumeric(Rtrim(ssn4))=1 then
Rtrim(ssn4)+REPLACE(CONVERT(VARCHAR(10), dob, 1), '/', '')
END
eND
eND
else
case When ssn4 is Not Null then
case when Isnumeric(Rtrim(ssn4))=1 then
Rtrim(ssn4)+REPLACE(CONVERT(VARCHAR(10), dob, 1), '/', '')
END
eND
end

End
as
'auto_dwid',
Case When ssn is Not Null then
Case when CHARINDEX('-',ssn) >0 then
ssn
End
End
'auto_ssn',
Case When ssn is Not Null then
case when Isnumeric(Rtrim(REPLACE(ssn,'-','')))=1 then
RIGHT(Rtrim(REPLACE(ssn,'-','')),4)
End
End

'auto_ssn4',
Dob as auto_dob,
case
When Relation='Employee' OR Relation='E' then
'E'
When Relation='SPOUSE' OR Relation='S' then
'S'
End
as auto_relation
from tbl_irpts_members






GO

Have you tried like this before?

CREATE VIEW [dbo].[ViewName]
AS


Select
Case When Len(Isnull(employee_id,''))=12 then
Case when Isnumeric(Left(Isnull(employee_id,''),4))=1
then
Left(Isnull(employee_id,''),4)
else
''
end
else
''
end as auto_ssn4,
Case When Len(Isnull(employee_id,''))=12 then
Case when ISDATE
(Left(Right(Isnull(employee_id,''),8),2)+'/'+
Left(Right(Isnull(employee_id,''),6),2)+'/'+
Right(Right(Isnull(employee_id,''),8),4))=1 then
Left(Right(Isnull(employee_id,''),8),2)
+
Left(Right(Isnull(employee_id,''),6),2)
+
Right(Right(Isnull(employee_id,''),8),4)
else
''
end
else
''
end as auto_dob
from tablename





GO

--View often for More Updates