SQL函数_小写金额转换成大写/********************************************************作者：版本：1.0创建时间：20020227修改时间：功能：小写金额转换成大写参数：n_LowerMoney 小写金额v_TransType 种类 -- 1: directly translate, 0: read it in words输出：大写金额********************************************************/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[L2U]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[L2U]GOCREATE FUNCTION dbo.L2U(@n_LowerMoney numeric(15,2),@v_TransType int)RETURNS VARCHAR(200) ASBEGINDeclare @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 = 1set @v_UpperStr = ''while ( @i_I <= len(@v_LowerStr))beginselect @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_IWHEN 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 ''ENDset @v_UpperStr = @v_UpperPart + @v_UpperStrset @i_I = @i_I + 1endif ( 0 = @v_TransType)beginset @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))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif ( '零' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))endif ( '角' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif ( '分' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif ('整' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = '零元整'endreturn @v_UpperStrENDGOselect dbo.L2U(12.93,1),dbo.L2U(12.93,0)CREATE PROCEDURE dbo.L2U(@n_LowerMoney numeric(15,2),@v_TransType int,@RET VARCHAR(200) output)ASDeclare @v_LowerStr VARCHAR(200) -- 小写金额Declare @v_UpperPart VARCHAR(200)Declare @v_UpperStr VARCHAR(200) -- 大写金额Declare @i_I intset nocount Onselect @v_LowerStr = LTRIM(RTRIM(STR(@n_LowerMoney,20,2))) --四舍五入为指定的精度并删除数据左右空格select @i_I = 1select @v_UpperStr = ''''while ( @i_I <= len(@v_LowerStr))beginselect @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_IWHEN 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''''ENDselect @v_UpperStr = @v_UpperPart + @v_UpperStrselect @i_I = @i_I + 1end--------print '//v_UpperStr ='+@v_UpperStr +'//'if ( @v_TransType=0 )beginselect @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')select @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')select @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')select @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元') select @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')select @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')select @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')select @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')select @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')select @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')select @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')select @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')end-- 对壹元以下的金额的处理if ( substring(@v_UpperStr,1,1)='元' )beginselect @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif (substring(@v_UpperStr,1,1)= '零')beginselect @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif (substring(@v_UpperStr,1,1)='角')beginselect @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1)) endif ( substring(@v_UpperStr,1,1)='分')beginselect @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))endif (substring(@v_UpperStr,1,1)='整')beginselect @v_UpperStr = '零元整'endselect @ret=@v_UpperStrGO--调用过程：declare @ret varchar(200)exec L2U 567983.897,1,@ret outputselect @retCREATE FUNCTION [dbo].[f_num_chn] (@num numeric(14,2))RETURNS varchar(100) WITH ENCRYPTIONASBEGIN--版权所有：pbsqlDECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i intSET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14) SET @c_data=''SET @i=1WHILE @i<=14BEGINSET @n_str=SUBSTRING(@n_data,@i,1)IF @n_str<>' 'BEGINIF not ((SUBSTRING(@n_data,@i,2)='00') or((@n_str='0') and ((@i=4) or (@i=8) or (@i=12) or (@i=14))))SET @c_data=@c_data+SUBSTRING('零壹贰叁肆伍陆柒捌玖',CAST(@n_str AS int)+1,1) IF not ((@n_str='0') and (@i<>4) and (@i<>8) and (@i<>12))SET @c_data=@c_data+SUBSTRING('仟佰拾亿仟佰拾万仟佰拾圆角分',@i,1)IF SUBSTRING(@c_data,LEN(@c_data)-1,2)='亿万'SET @c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)ENDSET @i=@i+1ENDIF @num<0SET @c_data='（负数）'+@c_dataIF @num=0SET @c_data='零圆'IF @n_str='0'SET @c_data=@c_data+'整'RETURN(@c_data)ENDGO。