Here is another solution that builds on the previous suggestions. This one handles a few more variations of the text data and the number to be added.

create table #tester

(seq tinyint null,

txt varchar(20) null,

num integer null)

set nocount on

insert #tester values (1,'ABC44',10)

insert #tester values (2,'ABC44',0)

insert #tester values (3,'',10)

insert #tester values (4,'ABC4',10)

insert #tester values (5,'ABC44',1000)

insert #tester values (6,'ABC',10)

insert #tester values (7,null,10)

insert #tester values (8,'ABC',null)

insert #tester values (9,null,null)

set nocount off

select seq,

case when isnull(txt,'') = '' then isnull(convert(varchar,num),'')

when patindex('%[0-9]%',txt) = 0 then txt + isnull(convert(varchar,num),'')

else substring(txt,1,patindex('%[0-9]%',txt) - 1) +

convert(varchar,convert(int,substring(txt, patindex('%[0-9]%',txt),len(txt)) + isnull(num,0)))

end as 'result'

from #tester

order by seq

drop table #tester

/*

seq result

---- --------------------------------------------------

1 ABC54

2 ABC44

3 10

4 ABC14

5 ABC1044

6 ABC10

7 10

8 ABC

9

(9 row(s) affected)

*/