sql2005中自带的人民币转为大写函数?求大神帮助

发布网友发布时间：2022-05-01 16:29

共2个回答

懂视网时间：2022-05-01 20:50

CREATE  FUNCTION [dbo].[f_num_chn] (@num numeric(14,5))
RETURNS varchar(100) WITH ENCRYPTION
AS
BEGIN
--版权所有：weihuachao@sina.com
  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

SQLSERVER中的人民币数字转大写的函数实现

标签：varchar str 函数 not bst 数字 int turn 圆角

热心网友时间：2022-05-01 17:58

说明: 1.本函数范围从毫 ~ 兆 2.有四种精度(元,角 ,分,厘 ,毫) 3.有三种进位规则(四舍五入,接舍去,非0就入) 参数说明:dbo.MoneyToCapital( 数值 , 进位 , 精度) 进位(0 四舍五入, 1 直接舍去,2 非0就入) 精确度 (0 元,1 角 ,2 分,3 厘 ,4 毫) -------------------------------------------------------------------------------------------------------- --测试数据: DECLARE @intNum decimal(38,4) SET @intNum = 1123456780.2154 --SET @intNum = 1001 --SET @intNum = 100100 --SET @intNum = 1005001 --SET @intNum = 100.11 --SET @intNum = 100.00 --SET @intNum = 100.01 SET @intNum = 99999999999999.9999 -- 最大 <1百兆(精确到毫) --SET @intNum = 10025.1234 --SET @intNum = 12345.6789 SELECT dbo.MoneyToCapital(@intNum,0,4) -------------------------------------------------------------------------------------------------------- */ CREATE FUNCTION MoneyToCapital ( @mnyNumber decimal(38,4), @intIsRound int = 0, -- 进位 (0 四舍五入, 1 直接舍去,2 非0就入) @intPrecision int = 2 -- 精确度: 0 元,1 角 ,2 分,3 厘 ,4 毫 ) RETURNS nvarchar(50) BEGIN DECLARE @strReturn nvarchar(50) DECLARE @strMoney varchar(50) DECLARE @intLen int DECLARE @strC1 char(1) DECLARE @strC2 char(1) DECLARE @strC3 char(1) DECLARE @intJ int DECLARE @necMoney decimal(38,4) DECLARE @strMoneyUnit nvarchar(50) DECLARE @strNumberCapital nvarchar(50) SET @strMoneyUnit = '毫厘分角元拾佰仟万拾佰仟亿拾佰仟兆拾佰仟京拾佰仟' SET @strNumberCapital = '零壹贰叁肆伍陆柒捌玖' --0 的情况 IF @mnyNumber = 0 BEGIN SET @strReturn = '零元整' RETURN @strReturn END --超出范围的情况 IF @mnyNumber < 0 OR @mnyNumber > 99999999999999.9999 BEGIN RETURN CAST(@mnyNumber AS varchar(50)) END ------进位超出范围 IF @intIsRound<0 OR @intIsRound>2 BEGIN SET @intIsRound = 0 END ------精确度超出范围 IF @intPrecision<0 OR @intPrecision>4 BEGIN SET @intPrecision = 2 END IF @intIsRound =1 BEGIN--直接舍去 SET @mnyNumber = ROUND(@mnyNumber,2,1) END ELSE IF @intIsRound = 2 BEGIN--非0就入 SET @mnyNumber = ROUND(@mnyNumber,2) END ELSE BEGIN--四舍五入 SET @mnyNumber = ROUND(@mnyNumber,@intPrecision) END SET @necMoney = @mnyNumber * POWER(10,@intPrecision) --精确度 @intPrecision SET @strMoney = CAST(CAST(@necMoney AS bigint) AS varchar(50)) SET @intLen = LEN(@strMoney) --长度 SET @strMoney = REVERSE(@strMoney) --逆转 SET @strReturn='' SET @intJ = 1 -- @intPrecision 精确度: 0 元,1 角 ,2 分,3 厘 ,4 毫( 1 开始对应毫) WHILE @intJ <= @intLen BEGIN SET @strC1 = SUBSTRING(@strMoney,@intJ-1,1) SET @strC2 = SUBSTRING(@strMoney,@intJ,1) SET @strC3 = SUBSTRING(@strMoney,@intJ+1,1) /* IF @strC2='0' BEGIN--当前数是 0 IF @strC1<>'0' AND @intJ<>1 BEGIN --前一个不是0则加 '零' SET @strReturn = dbo.NumberToCapital(@strC2) + @strReturn END END ELSE BEGIN SET @strReturn = dbo.NumberToCapital(@strC2) + dbo.GetMoneyUnit(@intJ,@strC1,@strC2) + @strReturn END */ SET @strReturn = SUBSTRING(@strNumberCapital , CAST(@strC2 AS int)+1 , 1) +SUBSTRING(@strMoneyUnit , @intJ+4-@intPrecision , 1) + @strReturn SET @intJ = @intJ + 1 END /* SET @strReturn = CAST(@strReturn AS varchar(100)) SET @strReturn = REPLACE(@strReturn,'零元','元') SET @strReturn = REPLACE(@strReturn,'零拾','拾') SET @strReturn = REPLACE(@strReturn,'零佰','佰') SET @strReturn = REPLACE(@strReturn,'零仟','仟') SET @strReturn = REPLACE(@strReturn,'零万','万') SET @strReturn = REPLACE(@strReturn,'零亿','亿') */ RETURN @strReturn END --String1 = "零壹贰叁肆伍陆柒捌玖" --String2 = "万仟佰拾亿仟佰拾万仟佰拾元角分厘毫"