USE tempdb;
GO
IF OBJECT_ID('tba') IS NOT NULL 
DROP TABLE tba;
GO
CREATE TABLE tba
(a1 INT ,
 a2 VARCHAR(20)
);
INSERT INTO tba VALUES (1,'rb1207');
INSERT INTO tba VALUES (2,'SB201207');
INSERT INTO tba VALUES (3,'RTS201209');
INSERT INTO tba VALUES (4,'p1212');

IF OBJECT_ID('tba') IS NOT NULL 
DROP TABLE tba;
GO
CREATE TABLE tba
(a1 INT ,
 a2 VARCHAR(20)
);
INSERT INTO tba VALUES (1,'rb1207');
INSERT INTO tba VALUES (2,'SB201207');
INSERT INTO tba VALUES (3,'RTS201209');
INSERT INTO tba VALUES (4,'p1212');

IF OBJECT_ID('dbo.fn_get_number') IS NOT NULL
DROP FUNCTION dbo.fn_get_number
GO
CREATE FUNCTION dbo.fn_get_number(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END

select right(dbo.get_number(a2),4)+200000 as new_a2 from tba 
/*
new_a2
201207
201207
201209
201212
*/

------解决方案--------------------
SQL code

IF OBJECT_ID('GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试
select a1,case when len(cast(DBO.GET_NUMBER2("a2") as char(6)))=4 then '20'+cast(DBO.GET_NUMBER2("a2") as char(6)) else cast(DBO.GET_NUMBER2("a2") as char(6)) end aa 
from tb
GO