存储过程,急啊,帮帮菜鸟吧...
5位的数据,前三位为数字,后二位为字母,如123AB
要用存储过程进行转换,规则为,前三位数字减19,后二位A对应S,B对应P,C对应......(24个字母按规则替换)如:123AB转换后为104SP
这批数据在test表的id字段中,用存储过程一次性更新
谢谢各位了!!!
[解决办法]
declare @s varchar(20)
set @s= '123AB '
select right( '000 '+cast(cast(left(@s,3) as int)-19 as varchar),3)+
case substring(@s,4,1) when 'A ' then 'S '
when 'B ' then 'P '
when 'C ' then ...
...
end
+case substring(@s,5,1) when 'A ' then 'S '
when 'B ' then 'P '
when 'C ' then ...
...
end
[解决办法]
select rtrim(left( '123AB ',3)-19)+replace(replace(replace(right( '123AB ',2), 'A ', 'S '), 'B ', 'P '), 'C ', '... ') ...
[解决办法]
----例子
create table t_t(a varchar(10),b varchar(10))
insert t_t
select 'A ', 'S '
union select 'B ', 'P '
create table test(id int,value varchar(10))
insert test
select 1, '123AB '
select right( '000 '+cast(left(value,3)-19 as varchar),3)+(select b from t_t where a=substring(value,4,1))+(select b from t_t where a=substring(value,5,1))
from test a
update test set value=right( '000 '+cast(left(value,3)-19 as varchar),3)+(select b from t_t where a=substring(value,4,1))+(select b from t_t where a=substring(value,5,1))
select * from test
drop t_t
drop table test
