create table Mytb(z1 varchar(18))
insert into Mytb values('一万三千六百七十三')
insert into Mytb values('一万三千六百七十')
insert into Mytb values('一万零六百七十三')
insert into Mytb values('一万十八')
insert into Mytb values('十八')
create function reInt(@Myii varchar(2))
returns int
as
begin
declare @iii int
set @iii=0
if(@Myii ='一')
set @iii=1
if(@Myii ='二')
set @iii=2
if(@Myii ='三')
set @iii=3
if(@Myii ='四')
set @iii=4
if(@Myii ='五')
set @iii=5
if(@Myii ='六')
set @iii=6
if(@Myii ='七')
set @iii=7
if(@Myii ='八')
set @iii=8
if(@Myii ='九')
set @iii=9
return (@iii)
end
go
--第一个函数你不需要用到,是我下面的函数要用
--drop function dbo.Myfun
create function Myfun(@ii varchar(20))
returns int
as
begin
declare @w int,@q int,@b int ,@s int,@g int
select @w=0,@q=0,@b=0,@s=0,@g=0
if(charindex('万',@ii)>0)
set @w=(dbo.reInt(left(@ii,1)))*10000
if(charindex('千',@ii)>0)
set @q=(dbo.reInt(right(left(@ii,charindex('千',@ii)-1),1)))*1000
if(charindex('百',@ii)>0)
set @b=(dbo.reInt(right(left(@ii,charindex('百',@ii)-1),1)))*100
if(charindex('十',@ii)>0)
begin
declare @Mychar varchar(2)
if(charindex('十',@ii)=1)
set @s=10
else
begin
set @MyChar=right(left(@ii,charindex('十',@ii)-1),1)
if(@Mychar='万' or @Mychar='千' or @Mychar='百' or @Mychar='零')
set @s=10
else
set @s=(dbo.reInt(@Mychar))*10
end
end
if(charindex('十',@ii)
set @g=dbo.reInt(right(@ii,1))
return (@w+@q+@b+@s+@g)
end
go
--exec sp_helptext Myfun
select * from Mytb
select dbo.Myfun(z1) from Mytb
测试结果:
z1
------------------
一万三千六百七十三
一万三千六百七十
一万零六百七十三
一万十八
十八
(所影响的行数为 5 行)
-----------
13673
13670
10673
10018
18
(所影响的行数为 5 行)