报错：Substring在将 varchar 值 '二〇二二' 转换成数据类型 int 时失败。

Question

报错：在将 varchar 值 '二〇二二' 转换成数据类型 int 时失败。
代码如下：

declare @Announmt2 varchar(200)
set @Announmt2='中远海能：二〇二二年半年度业绩预减公告'
select convert(int,Substring(right(@Announmt2,(len(@Announmt2)-len(Substring(@Announmt2,0,CHARINDEX(':',@Announmt2)+1)))),CHARINDEX('年',right(@Announmt2,(len(@Announmt2)-len(Substring(@Announmt2,0,CHARINDEX(':',@Announmt2)+1)))))-4,4))+1

想得到的结果为：2023
请教一下大家专门解决这个问题~

Answer 1

注意数据库的collate要为Chinese_PRC_BIN,要不然中文〇替换不了

declare @Announmt2 varchar(200),@str varchar(200);
set @Announmt2='中远海能：二〇二二年半年度业绩预减公告';
set @str=Substring(right(@Announmt2,(len(@Announmt2)-len(Substring(@Announmt2,0,CHARINDEX(':',@Announmt2)+1)))),CHARINDEX('年',right(@Announmt2,(len(@Announmt2)-len(Substring(@Announmt2,0,CHARINDEX(':',@Announmt2)+1)))))-4,4);
set @str= replace(@str,'〇','0');
set @str= replace(@str,'一','1');
set @str= replace(@str,'二','2');
set @str= replace(@str,'三','3');
set @str= replace(@str,'四','4');
set @str= replace(@str,'五','5');
set @str= replace(@str,'六','6');
set @str= replace(@str,'七','7');
set @str= replace(@str,'八','8');
set @str= replace(@str,'九','9');
select convert(int,@str)+1