/* */if exists(select * from sysobjects
where name = 'num_spell' and type = 'P' and uid = User_Id() )
drop proc num_spell
gocreate proc num_spell (@num_dig int, @spell varchar(15) OUTPUT)
as
declare @rem int, @num int
select @spell = ''
if (@num_dig > 19)
begin
select @num = @num_dig / power(10,1), @rem = @num_dig % power(10,1)
select @spell = case @num
when 2 then 'Twenty '
when 3 then 'Thirty '
when 4 then 'Forty '
when 5 then 'Fifty '
when 6 then 'Sixty '
when 7 then 'Seventy '
when 8 then 'Eighty '
when 9 then 'Ninety '
end
select @num_dig = @rem
end select @spell = @spell + case @num_dig
when 1 then 'One'
when 2 then 'Two'
when 3 then 'Three'
when 4 then 'Four'
when 5 then 'Five'
when 6 then 'Six'
when 7 then 'Seven'
when 8 then 'Eight'
when 9 then 'Nine'
when 10 then 'Ten'
when 11 then 'Eleven'
when 12 then 'Twelve'
when 13 then 'Thirteen'
when 14 then 'Fourteen'
when 15 then 'Fifteen'
when 16 then 'Sixteen'
when 17 then 'Seventeen'
when 18 then 'Eighteen'
when 19 then 'Nineteen'
endgoif exists(select * from sysobjects
where name = 'num_proc_UK' and type = 'P' and uid = User_Id() )
drop proc num_proc_UK
gocreate proc num_proc_UK (@num int = 0 OUTPUT, @word varchar(255) OUTPUT)
as
declare
@len int, @num_dig int, @sqlstr varchar(255), @spell varchar(15)
begin
select @len = case
when @num >= 10000000 then 8
when @num >= 100000 then 6
when @num >= 1000 then 4
when @num >= 100 then 3
when @num >= 10 then 2
when @num > 0 then 1
end
if @len > 2
select @num_dig = @num / power(10,@len-1),
@num = @num % power(10,@len-1)
else
select @num_dig = @num if @num_dig <= 99
begin
execute num_spell @num_dig, @spell OUTPUT
select @word = case when @len <= 2 then
case when @word != '' then @word + ' and '
else @word
end
else @word
end
+ @spell
end
else
begin
while @num_dig > 0
execute num_proc_UK @num_dig OUTPUT, @word OUTPUT
end if @len >= 8
select @word = @word + ' Crore '
else if @len >= 6
select @word = @word + ' Lakh '
else if @len >= 4
select @word = @word + ' Thousand '
else if @len = 3
select @word = @word + ' Hundred'
else
select @num = 0
endgoif exists(select * from sysobjects
where name = 'num_proc_US' and type = 'P' and uid = User_Id() )
drop proc num_proc_US
gocreate proc num_proc_US (@num int = 0 OUTPUT, @word varchar(255) OUTPUT)
as
declare
@len int, @num_dig int, @sqlstr varchar(255), @spell varchar(15)
begin
select @len = case
when @num >= 1000000000 then 10
when @num >= 1000000 then 7
when @num >= 1000 then 4
when @num >= 100 then 3
when @num >= 10 then 2
when @num > 0 then 1
end
if @len > 2
select @num_dig = @num / power(10,@len-1),
@num = @num % power(10,@len-1)
else
select @num_dig = @num if @num_dig <= 99
begin
execute num_spell @num_dig, @spell OUTPUT
select @word = case
when @len <= 2 then
case
when @word != '' then @word + ' and '
else @word
end
else @word
end
+ @spell
end
else
begin
while @num_dig > 0
execute num_proc_US @num_dig OUTPUT, @word OUTPUT
end if @len >= 10
select @word = @word + ' Billion '
else if @len >= 7
select @word = @word + ' Million '
else if @len >= 4
select @word = @word + ' Thousand '
else if @len = 3
select @word = @word + ' Hundred'
else
select @num = 0
endgoif exists(select * from sysobjects
where name = 'num_to_words' and type = 'P' and uid = User_Id() )
drop proc num_to_words
goCreate proc num_to_words(@number int = 0,
@word varchar(255) OUTPUT, @type varchar(2))
as
declare
@num int, @len int, @len1 int, @num_dig int, @num_dig1 int,
@sign_flag char(1)If @type not in ('US' , 'UK')
Begin
RAISERROR ('Unknown type, type should be US or UK',10,-1)
Return(-1)
Endselect @num = @number
if (@num < 0)
begin
select @sign_flag = 'M'
select @num = -(@num)
end
if (@num = 0)
begin
select @word = 'Zero'
return
end
select @word = ''
while @num > 0
begin
if @type = 'UK'
execute num_proc_UK @num OUTPUT, @word OUTPUT
else if @type = 'US'
execute num_proc_US @num OUTPUT, @word OUTPUT
endif (@sign_flag = 'M')
select @word = 'Minus ' + @wordgo------------------------------------------------------------------------
/*Declare @str varchar(255)
Exec num_to_words 12345678 , @str output, 'UK'
select @str
Exec num_to_words 12345678 , @str output, 'US'
select @str*/
/*
Nipsan Chow
2001/04/12
JZC Software mail: [email protected]
*/
where name = 'num_spell' and type = 'P' and uid = User_Id() )
drop proc num_spell
gocreate proc num_spell (@num_dig int, @spell varchar(15) OUTPUT)
as
declare @rem int, @num int
select @spell = ''
if (@num_dig > 19)
begin
select @num = @num_dig / power(10,1), @rem = @num_dig % power(10,1)
select @spell = case @num
when 2 then 'Twenty '
when 3 then 'Thirty '
when 4 then 'Forty '
when 5 then 'Fifty '
when 6 then 'Sixty '
when 7 then 'Seventy '
when 8 then 'Eighty '
when 9 then 'Ninety '
end
select @num_dig = @rem
end select @spell = @spell + case @num_dig
when 1 then 'One'
when 2 then 'Two'
when 3 then 'Three'
when 4 then 'Four'
when 5 then 'Five'
when 6 then 'Six'
when 7 then 'Seven'
when 8 then 'Eight'
when 9 then 'Nine'
when 10 then 'Ten'
when 11 then 'Eleven'
when 12 then 'Twelve'
when 13 then 'Thirteen'
when 14 then 'Fourteen'
when 15 then 'Fifteen'
when 16 then 'Sixteen'
when 17 then 'Seventeen'
when 18 then 'Eighteen'
when 19 then 'Nineteen'
endgoif exists(select * from sysobjects
where name = 'num_proc_UK' and type = 'P' and uid = User_Id() )
drop proc num_proc_UK
gocreate proc num_proc_UK (@num int = 0 OUTPUT, @word varchar(255) OUTPUT)
as
declare
@len int, @num_dig int, @sqlstr varchar(255), @spell varchar(15)
begin
select @len = case
when @num >= 10000000 then 8
when @num >= 100000 then 6
when @num >= 1000 then 4
when @num >= 100 then 3
when @num >= 10 then 2
when @num > 0 then 1
end
if @len > 2
select @num_dig = @num / power(10,@len-1),
@num = @num % power(10,@len-1)
else
select @num_dig = @num if @num_dig <= 99
begin
execute num_spell @num_dig, @spell OUTPUT
select @word = case when @len <= 2 then
case when @word != '' then @word + ' and '
else @word
end
else @word
end
+ @spell
end
else
begin
while @num_dig > 0
execute num_proc_UK @num_dig OUTPUT, @word OUTPUT
end if @len >= 8
select @word = @word + ' Crore '
else if @len >= 6
select @word = @word + ' Lakh '
else if @len >= 4
select @word = @word + ' Thousand '
else if @len = 3
select @word = @word + ' Hundred'
else
select @num = 0
endgoif exists(select * from sysobjects
where name = 'num_proc_US' and type = 'P' and uid = User_Id() )
drop proc num_proc_US
gocreate proc num_proc_US (@num int = 0 OUTPUT, @word varchar(255) OUTPUT)
as
declare
@len int, @num_dig int, @sqlstr varchar(255), @spell varchar(15)
begin
select @len = case
when @num >= 1000000000 then 10
when @num >= 1000000 then 7
when @num >= 1000 then 4
when @num >= 100 then 3
when @num >= 10 then 2
when @num > 0 then 1
end
if @len > 2
select @num_dig = @num / power(10,@len-1),
@num = @num % power(10,@len-1)
else
select @num_dig = @num if @num_dig <= 99
begin
execute num_spell @num_dig, @spell OUTPUT
select @word = case
when @len <= 2 then
case
when @word != '' then @word + ' and '
else @word
end
else @word
end
+ @spell
end
else
begin
while @num_dig > 0
execute num_proc_US @num_dig OUTPUT, @word OUTPUT
end if @len >= 10
select @word = @word + ' Billion '
else if @len >= 7
select @word = @word + ' Million '
else if @len >= 4
select @word = @word + ' Thousand '
else if @len = 3
select @word = @word + ' Hundred'
else
select @num = 0
endgoif exists(select * from sysobjects
where name = 'num_to_words' and type = 'P' and uid = User_Id() )
drop proc num_to_words
goCreate proc num_to_words(@number int = 0,
@word varchar(255) OUTPUT, @type varchar(2))
as
declare
@num int, @len int, @len1 int, @num_dig int, @num_dig1 int,
@sign_flag char(1)If @type not in ('US' , 'UK')
Begin
RAISERROR ('Unknown type, type should be US or UK',10,-1)
Return(-1)
Endselect @num = @number
if (@num < 0)
begin
select @sign_flag = 'M'
select @num = -(@num)
end
if (@num = 0)
begin
select @word = 'Zero'
return
end
select @word = ''
while @num > 0
begin
if @type = 'UK'
execute num_proc_UK @num OUTPUT, @word OUTPUT
else if @type = 'US'
execute num_proc_US @num OUTPUT, @word OUTPUT
endif (@sign_flag = 'M')
select @word = 'Minus ' + @wordgo------------------------------------------------------------------------
/*Declare @str varchar(255)
Exec num_to_words 12345678 , @str output, 'UK'
select @str
Exec num_to_words 12345678 , @str output, 'US'
select @str*/
/*
Nipsan Chow
2001/04/12
JZC Software mail: [email protected]
*/
RETURNS VARCHAR(200) AS
BEGIN
Declare @v_LowerStr VARCHAR(200) -- 小写金额
Declare @v_UpperPart VARCHAR(200)
Declare @v_UpperStr VARCHAR(200) -- 大写金额
Declare @i_I intset @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2))) --四舍五入为指定的精度并删除数据左右空格
set @i_I = 1
set @v_UpperStr = ''while ( @i_I <= len(@v_LowerStr))
begin
select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)
WHEN '.' THEN '元'
WHEN '0' THEN '零'
WHEN '1' THEN '壹'
WHEN '2' THEN '贰'
WHEN '3' THEN '叁'
WHEN '4' THEN '肆'
WHEN '5' THEN '伍'
WHEN '6' THEN '陆'
WHEN '7' THEN '柒'
WHEN '8' THEN '捌'
WHEN '9' THEN '玖'
END
+
case @i_I
WHEN 1 THEN '分'
WHEN 2 THEN '角'
WHEN 3 THEN ''
WHEN 4 THEN ''
WHEN 5 THEN '拾'
WHEN 6 THEN '佰'
WHEN 7 THEN '仟'
WHEN 8 THEN '万'
WHEN 9 THEN '拾'
WHEN 10 THEN '佰'
WHEN 11 THEN '仟'
WHEN 12 THEN '亿'
WHEN 13 THEN '拾'
WHEN 14 THEN '佰'
WHEN 15 THEN '仟'
WHEN 16 THEN '万'
ELSE ''
END
set @v_UpperStr = @v_UpperPart + @v_UpperStr
set @i_I = @i_I + 1
endif ( 0 = @v_TransType)
begin
set @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')
set @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')
set @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元')
set @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')
set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')
set @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')
set @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')
set @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')
set @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')
set @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')
set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
end-- 对壹元以下的金额的处理
if ( '元' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
endif ( '零' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
endif ( '角' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
endif ( '分' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
endif ('整' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = '零元整'
end
return @v_UpperStr
END