设为首页 加入收藏
当前位置:首页 > 整站内容 > 网站建设 > 服务器技术 > 详细内容
[SQL SERVER函数将人民币数字转换成大写形式]
发布时间:2014-6-17  阅读次数:7095  字体大小: 【】 【】【

=================================================================

SQL SERVER函数将人民币数字转换成大写形式

  CREATE   FUNCTION [dbo].[f_num_chn] (@num numeric(14,5))

  RETURNS varchar(100) WITH ENCRYPTION

  AS

  BEGIN

  DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int

  SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14)

  SET @c_data=''

  SET @i=1

  WHILE @i<=14

  BEGIN

  SET @n_str=SUBSTRING(@n_data,@i,1)

  IF @n_str<>' '

  BEGIN

  IF 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)

  END

  SET @i=@i+1

  END

  IF @num<0

  SET @c_data='(负数)'+@c_data

  IF @num=0

  SET @c_data='零圆'

  IF @n_str='0'

  SET @c_data=@c_data+'整'

  RETURN(@c_data)

  END

  SQL SERVER函数将人民币数字转换成大写形式

  闲着也是闲着,欢迎大家多提宝贵意见

  --阿拉伯数字转中文大写数字

  Create     function NToCC(@n int)

  returns varchar(2)

  as

  begin

  declare @s varchar(2)

  if @n=0

  set @s=''零''

  if @n=1

  set @s=''壹''

  if @n=2

  set @s=''贰''

  if @n=3

  set @s=''叁''

  if @n=4

  set @s=''肆''

  if @n=5

  set @s=''伍''

  if @n=6

  set @s=''陆''

  if @n=7

  set @s=''柒''

  if @n=8

  set @s=''捌''

  if @n=9

  set @s=''玖''

  return @s

  end

  --可支持到上亿

  Create     function numbertoChineseBigString(@m money)

  returns varchar(255)

  begin

  declare @i varchar(255)

  declare @f varchar(255)

  declare @r varchar(255)

  declare @s varchar(255)

  set @s=convert(varchar(255),@m)

  if charindex(''.'',@s)>0

  begin

  set @i=substring(@s,1,charindex(''.'',@s)-1)

  set @f=substring(@s,charindex(''.'',@s)+1,len(@s))

  end

  else

  set @i=@s

  set @r=''''

  declare @k int

  set @k=len(@i)

  while @k>=1

  begin

  --最多9位

  if @k=9

  set @r=@r+

  dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))

  --@i

  +''亿''

  if @k=8

  set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''千''

  if @k=7

  set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''佰''

  if @k=6

  set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''拾''

  if @k=5

  set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''万''

  if @k=4

  set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''千''

  if @k=3

  set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''佰''

  if @k=2

  set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''拾''

  if @k=1

  set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''元''

  set @k=@k-1

  end

  if convert(float,@f)=0

  set @r=@r+''整''

  else

  begin

  if substring(@s,1,1)>''''

  set @r=@r+dbo.NToCC(convert(int,substring(@f,1,1)))+''角''

  if substring(@s,2,1)>''''

  set @r=@r+dbo.NToCC(convert(int,substring(@f,2,1)))+''分''

  end

set @r=replace(@r,''零千零佰零拾'',''零'')

  set @r=replace(@r,''零佰零拾'',''零'')

  set @r=replace(@r,''零千零佰'',''零'')

  set @r=replace(@r,''零拾'',''零'')

  set @r=replace(@r,''零千'',''零'')

  set @r=replace(@r,''零佰'',''零'')

  set @r=replace(@r,''拾零万'',''拾万'')

  return @r

  end

  --         select dbo.numbertoChineseBigString(100102)

  CREATE FUNCTION dbo.L2U(@n_LowerMoney numeric(15,2),@v_TransType int)

  RETURNS VARCHAR(200) AS

  BEGIN

  Declare @v_LowerStr VARCHAR(200) -- 小写金额

  Declare @v_UpperPart VARCHAR(200)

  Declare @v_UpperStr VARCHAR(200) -- 大写金额

  Declare @i_I int

  set @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

  end

  if ( 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))

  end

  if ( '零' = substring(@v_UpperStr,1,1))

  begin

  set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

  end

  if ( '角' = substring(@v_UpperStr,1,1))

  begin

  set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

  end

  if ( '分' = substring(@v_UpperStr,1,1))

  begin

  set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))

  end

  if ('整' = substring(@v_UpperStr,1,1))

  begin

  set @v_UpperStr = '零元整'

  end

  return @v_UpperStr

  END

===============================================================

天猫商城    聚划算    天猫电器城    淘宝旅行    天猫超市    淘宝游戏    天猫品牌特卖    天猫母婴

我要评论
  • 匿名发表
  • [添加到收藏夹]
  • 发表评论:(匿名发表无需登录,已登录用户可直接发表。) 登录状态:未登录
最新评论
所有评论[0]
    暂无已审核评论!

全站链接: 
收录查询
分享按钮