寫了個小寫轉大寫的sp ,0.1 版本繼續測試中

--更換的最新版本
--另外臨時表可以考慮改成永久表

if exists (select * from sysobjects where name = 'sp_convertmoney' and type = 'P')
drop procedure sp_convertmoney
go
create procedure sp_convertmoney
@convmoney numeric(12,2)
as
begin
set nocount on
create table #tmp0 --大小寫對比表
(id numeric(2,0) identity not null,
digital char(1) not null,
dx char(2) not null,

insert into #tmp0(digital,dx) values('1','壹')
insert into #tmp0(digital,dx) values('2','貳')
insert into #tmp0(digital,dx) values('3','三')
insert into #tmp0(digital,dx) values('4','肆')
insert into #tmp0(digital,dx) values('5','伍')
insert into #tmp0(digital,dx) values('6','陸')
insert into #tmp0(digital,dx) values('7','柒')
insert into #tmp0(digital,dx) values('8','捌')
insert into #tmp0(digital,dx) values('9','玖')
insert into #tmp0(digital,dx) values('0','零')

create table #tmp1(
id numeric(2,0) identity not null, --序列號碼
pos1 integer not null, --數據位置號
num char(1) null , --待轉換的數據
unit char(2) null , --單位
dx char(2) null --保存該數據的大寫
)
insert into #tmp1 (pos1,unit) values(-2,'分')
insert into #tmp1 (pos1,unit) values(-1,'角')
insert into #tmp1 (pos1,unit) values(0,' ')
insert into #tmp1 (pos1,unit) values(1,'元')
insert into #tmp1 (pos1,unit) values(2,'拾')
insert into #tmp1 (pos1,unit) values(3,'佰')
insert into #tmp1 (pos1,unit) values(4,'仟')
insert into #tmp1 (pos1,unit) values(5,'萬')
insert into #tmp1 (pos1,unit) values(6,'拾')
insert into #tmp1 (pos1,unit) values(7,'佰')
insert into #tmp1 (pos1,unit) values(8,'仟')
insert into #tmp1 (pos1,unit) values(9,'億')
insert into #tmp1 (pos1,unit) values(10,'拾')
insert into #tmp1&nb
您正在看的Sybase教程是:寫了個小寫轉大寫的sp ,0.1 版本繼續測試中。sp;(pos1,unit) values(11,'佰')
insert into #tmp1 (pos1,unit) values(12,'仟')

declare @strmoney varchar(15),

--聲明字符型變量保存輸入金額
@revsstrmoney varchar(15), --逆序字符
@len0 smallint, --全部數字長度
@adig char(1), --存放單個數字
@i integer
select @i= 1
select @convmoney = convert(numeric(12,2),@convmoney) --強制轉換為 numeric(12,2)格式
select @strmoney = convert(varchar(15),@convmoney)
select @revsstrmoney = reverse(@strmoney)
select @len0 = datalength(@strmoney)
while @i<=@len0
begin
select @adig = substring(@revsstrmoney,@i,1)
update #tmp1 set num = @adig where id = @i
select @i = @i+1
end

update #tmp1 set dx = b.dx from #tmp1 a , #tmp0 b
where a.num = b.digital
/*
select id,num,dx,unit,dx+unit as dx_unit from #tmp1 where num>='0' and num<='9'
order by id desc
*/

declare @dx_unit varchar(12) ,@result varchar(255)
select @result = ''

declare cursor1 cursor for
select dx+unit as dx_unit from #tmp1 where num>='0' and num<='9'
order by id desc for read only
open cursor1

fetch cursor1 into @dx_unit
while @@SQLSTATUS =0
begin
select @result = @result +ltrim(rtrim(@dx_unit))

fetch cursor1 into @dx_unit
end
close cursor1
DEALLOCATE cursor cursor1

--select @result
while(charindex('零億',@result)>0)
begin
select @result = stuff(@result,charindex('零億',@result) ,4,'億')
end


while(charindex('零萬',@result)>0)
begin
select @result = stuff(@result,charindex('零萬',@result) ,4,'萬')
end

while(charindex('零仟',@result)>0)
begin
select @result = stuff(@result,charindex('零仟',@result) ,4,'零')
end
while(charindex('零佰',@result)>0)
begin
select @result = stuff(@result,charindex('零佰',@result) ,4,'零')
end
while(charindex('零拾',@result)>0)
begin
select @result

您正在看的Sybase教程是:寫了個小寫轉大寫的sp ,0.1 版本繼續測試中。= stuff(@result,charindex('零拾',@result) ,4,'零')
end
while(charindex('零元',@result)>0)
begin
select @result = stuff(@result,charindex('零元',

@result) ,4,'元')
end
while(charindex('零萬',@result)>0)
begin
select @result = stuff(@result,charindex('零萬',@result) ,4,'萬')
end

while(charindex('零角',@result)>0)
begin
select @result = stuff(@result,charindex('零角',@result) ,4,'零')
end
while(charindex('零分',@result)>0)
begin
select @result = stuff(@result,charindex('零分',@result) ,4,'零')
end

while(charindex('零零',@result)>0)
begin
select @result = stuff(@result,charindex('零零',@result) ,4,'零')
end

while(charindex('億萬',@result)>0)
begin
select @result = stuff(@result,charindex('億萬',@result) ,4,'億')
end



if @convmoney = convert(numeric(12,0),@convmoney) --整數
select @result = substring( @result , 1 , charindex('元',@result)+1)+'整'
select @result= ltrim(rtrim(@result))

--解決只有小數的情況
select @result = ltrim(rtrim(@result))
if

@result like '元%'
begin
select @result =substring( @result , 3 ,datalength(@result)-2)
select @result= ltrim(rtrim(@result))
end
--去第一個零
if @result like '零%'
begin
select @result = substring( @result , 3,datalength(@result)-2)
select @result = ltrim(rtrim(@result))
end

--去最後一個零
if @result like '%零'
begin
select @result = substring( @result , 1,datalength(@result)-2)
select @result = ltrim(rtrim(@result))
end
select @result
end