create function f_str(@str varchar(30))
returns varchar(60)
as
begin
declare @n_str varchar(60)
set @n_str=''
while isnumeric(@str)=0
begin
select @n_str=@n_str+(case left(@str,1)
when 'A' then '11'
when 'B' then '12'
end),
@str =stuff(@str,1,1,'')
end
set @n_str=@n_str+rtrim(@str*2) return @n_str
end
goselect dbo.f_str('AB1234')
/*
------------------------------------------------------------
11122468
*/
godrop function f_str
go
returns varchar(60)
as
begin
declare @n_str varchar(60)
set @n_str=''
while isnumeric(@str)=0
begin
select @n_str=@n_str+(case left(@str,1)
when 'A' then '11'
when 'B' then '12'
end),
@str =stuff(@str,1,1,'')
end
set @n_str=@n_str+rtrim(@str*2) return @n_str
end
goselect dbo.f_str('AB1234')
/*
------------------------------------------------------------
11122468
*/
godrop function f_str
go
returns varchar(60)
as
begin
declare @n_str varchar(60)
set @n_str=''
while isnumeric(@str)=0
begin
select @n_str=@n_str+rtrim(ascii(left(@str,1))-54), --英文字母根据ASCII码转换
@str =stuff(@str,1,1,'')
end
set @n_str=@n_str+rtrim(@str*2) return @n_str
end
goselect dbo.f_str('AB1234')
/*
------------------------------------------------------------
11122468
*/
godrop function f_str
go
因为今天就要交差,能否帮忙update语句写一下,表名test,字段就一个id,就是AB转化后,整个数字11121234开根号,更新,麻烦麻烦!!!!