update yourtable set recordnumber1=cast(substring('京移计[2000]17号',charindex(']','京移计[2000]17号')+1,len('京移计[2000]17号')-1-charindex(']','京移计[2000]17号')) as int)
-------->
update yourtable set recordnumber1=cast(substring(yourcolumn,charindex(']',yourcolumn)+1,len(yourcolumn)-1-charindex(']',yourcolumn)) as int)
--先建立函数
create function getint(@str nvarchar(100),@substr nvarchar(100))
returns int
AS
begin
declare @x int,@sstr nvarchar(1),@str1 nvarchar(50)
SET @x=charindex(@substr,@str)+len(@substr)
SET @str1=''
while @x<len(@str)
begin
select @sstr=substring(@str,@x,1)
if @sstr in ('0','1','2','3','4','5','6','7','8','9')
set @str1 =@str1+@sstr
else break
set @x=@x+1
end
if @str1='' set @str1='0'
return cast(@str1 as int)
end
然后用更新语句
update 表
set r2='1999',
r3=dbo.getint(r1,'(1999)'),--我的括号是全角的括号,也就是中文括号
t1='信息',
t2='2000',
t3=dbo.getint(r1,'(2000)')