用REPLACE截取""前的字符串最后一位数字-1解决办法_Sql Server

用REPLACE截取"_"前的字符串最后一位数字-1
我想在问一下select REPLACE('1076D67D71_0_10516-13','_0_10516-13','') 如何让最后一位-1呢

------解决方案--------------------

SQL code

select left(REPLACE('1076D67D71_0_10516-13','_0_10516-13',''),8)+ convert(varchar(2),cast(right(REPLACE('1076D67D71_0_10516-13','_0_10516-13',''),2) as int)-1)--后两位是数字的可用
------解决方案--------------------

SELECT right(REPLACE('1076D67D71_0_10516-13','_0_10516-13',''),(LEN(REPLACE('1076D67D71_0_10516-13','_0_10516-13',''))-1))+'-'+LEFT(REPLACE('1076D67D71_0_10516-13','_0_10516-13',''),1)
------解决方案--------------------
SQL codeSELECT right(REPLACE('1076D67D71_0_10516-13','_0_10516-13',''),(LEN(REPLACE('1076D67D71_0_10516-13','_0_10516-13',''))-1))+'-'+LEFT(REPLACE('1076D67D71_0_10516-13','_0_10516-13',''),1)
------解决方案--------------------
SQL codedeclare @s varchar(100)set @s='10A78D0010_0_10516-13'declare @tmp1 varchar(100),@tmp2 varchar(100), @num int,@re varchar(100)select @tmp1=left(@s,case when charindex('_',@s)-1 > 0 then charindex('_',@s)-1 else len(@s) end),@tmp2= left(reverse(@tmp1), case when patindex('%[^0-9]%',reverse(@tmp1))-1>0 then patindex('%[^0-9]%',reverse(@tmp1))-1 else len(@tmp1) end),@num=convert(int,reverse(@tmp2))-1,@re= stuff(@tmp1,len(@tmp1)-len(@tmp2)+1,len(@tmp2),right(replace(space(len(@tmp2)),' ','0')+rtrim(@num),len(@tmp2)))print @re/*10A78D0009*/