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 code

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 code



declare @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
*/