Create function test(@a varchar(20))
returns varchar(20)
as
begin
declare @i int,@b varchar(20)
set @i = 1
set @b = ''
while substring(@a,@i,1) <> ''
begin
if substring(@a,@i,1) between '0' and '9' or upper(substring(@a,@i,1)) between 'A' and 'Z'
set @b = @b + substring(@a,@i,1)
set @i = @i + 1
end
return(@b)
endupdate yourTable set columnB = dbo.Test(columnA)
returns varchar(20)
as
begin
declare @i int,@b varchar(20)
set @i = 1
set @b = ''
while substring(@a,@i,1) <> ''
begin
if substring(@a,@i,1) between '0' and '9' or upper(substring(@a,@i,1)) between 'A' and 'Z'
set @b = @b + substring(@a,@i,1)
set @i = @i + 1
end
return(@b)
endupdate yourTable set columnB = dbo.Test(columnA)
解决方案 »
- Sql server 2005 附加数据库时出现的问题,急
- 菜鸟请各位路过的帮忙看看这句sql是什么意思?
- sqlserver2000 与oracle 9i(10g)的全文检索谁的效率更高
- 请问如何把一台机器上的数据库复制到另外一台机器上啊?
- SQR SERVER转换成MySQL?
- 高手请进,一个棘手的数据库事务与非数据库的协同问题!
- sqlserver安全性太差,能不能不让别人把我的库拷贝或者附加走呀?????
- 表与表之间的关联问题?
- 为什么SQL Server 远程管理查询很快,但客户端很慢?
- 新手求助:sql统计查询结果汇总行怎么写???谢谢!!!
- 创建数据库
- 怎样删除指定行中指定列的内容
returns varchar(1000)
as
begin
declare @a varchar(1000)
while len(@)>0
begin
select @a=@a+left(@,1) where left(@,1) like '[0-9A-z]'
set @=right(@,len(@)-1)
end
return @a
end
go
--调用:
select columnA,dbo.getstr(columnA) columnB from 一个表--如果是update
update 一个表 set columnB=dbo.getstr(columnA)
create table test2(columnA varchar(100),columnB varchar(100) null)
insert test2(columnA) values('123/DKKD ')
insert test2(columnA) values('KKKD%334')
insert test2(columnA) values('KKD-34JK')
go
create function f_test2(@a varchar(8000))
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
declare @i int
set @i=1
set @a=ltrim(rtrim(@a))
while @i<=len(@a)
begin
if (substring(@a,@i,1) between '0' and '9') or (upper(substring(@a,@i,1)) between 'A' and 'Z')
set @sql=@sql+substring(@a,@i,1)
set @i=@i+1
end
return(@sql)
end
go
update test2 set columnb=dbo.f_test2(columnA)
go
select * from test2
然后运行
update 一个表 set columnB=dbo.getstr(columnA)