with cte as (select '16-5' as lh,'02' as ylh union all select '16-12' as lh,'02' as ylh union all select '17-8' as lh,'09' as ylh union all select '11-02' as lh,'05' as ylh union all select '19' as lh,'01' as ylh union all select '221-3' as lh,'11' as ylh ) select case when CHARINDEX('-',lh)=0 then lh when LEN(lh)-CHARINDEX('-',lh)=1 then REPLACE(lh,'-','0') when LEN(lh)-CHARINDEX('-',lh)=2 then REPLACE(lh,'-','') end as lh,convert(int,ylh) as yhl from cte --结果 lh yhl ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- 1605 2 1612 2 1708 9 1102 5 19 1 22103 11(6 行受影响)
with cte as
(select '16-5' as lh,'02' as ylh union all
select '16-12' as lh,'02' as ylh union all
select '17-8' as lh,'09' as ylh union all
select '11-02' as lh,'05' as ylh union all
select '19' as lh,'01' as ylh union all
select '221-3' as lh,'11' as ylh )
select case when CHARINDEX('-',lh)=0 then lh
when LEN(lh)-CHARINDEX('-',lh)=1 then REPLACE(lh,'-','0')
when LEN(lh)-CHARINDEX('-',lh)=2 then REPLACE(lh,'-','')
end as lh,convert(int,ylh) as yhl from cte --结果
lh yhl
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
1605 2
1612 2
1708 9
1102 5
19 1
22103 11(6 行受影响)