declare @t table(patient_id varchar(20))
insert @t
select '001235600000' union all
select '00123570000' union all
select 'A0123569800'
select
case when substring(patient_id,1,1)='A' then patient_id
else cast(cast(substring(patient_id,2,10) as int) as varchar) end patient_id
from @t
insert @t
select '001235600000' union all
select '00123570000' union all
select 'A0123569800'
select
case when substring(patient_id,1,1)='A' then patient_id
else cast(cast(substring(patient_id,2,10) as int) as varchar) end patient_id
from @t
使数据类型混在一起了 导致字段串向int转换出错
select
case when substring(patient_id,1,1)='A' then patient_id
else cast(cast(substring(patient_id,2,10) as int)/100 as varchar) end patient_id
from @t
如果patient_id都是以00开头00结尾
select
case when substring(mz_patient_mi.patient_id,1,1)='A' then mz_patient_mi.patient_id
else substring(mz_patient_mi.patient_id,3,len(mz_patient_mi.patient_id)-4)end patient_id
from mz_patient_mi
如果patient_id都是以00开头结尾不定
select
case when substring(mz_patient_mi.patient_id,1,1)='A' then mz_patient_mi.patient_id
else substring(mz_patient_mi.patient_id,3,len(mz_patient_mi.patient_id)-4)+'.'+right(mz_patient_mi.patient_id,2) end patient_id
from mz_patient_mi