数字金额转换成中文金额(数据库实现)

利用函数实现,以Sql server2000为基础实现。 利用函数实现,以Sql server2000为基础实现。

Create     FUNCTION UF_FormatDecimalToChinese (@ConvertValue numeric(15,2)) 
RETURNS VARCHAR(200) AS

BEGIN
Declare @LowString  VARCHAR(200) 
Declare @UpString   VARCHAR(200)
Declare @UpPart     VARCHAR(200)
Declare @i          int

Set @LowString  = LTRIM(RTRIM(ROUND(@ConvertValue,2)))
Set @i = 1
Set @UpString   = ''

While ( @i <= len(@LowString))
Begin
      Select @UpPart = case substring(@LowString,len(@LowString) - @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
                            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 @UpString = @UpPart + @UpString
    Set @i = @i + 1
End


Set @UpString = REPLACE(@UpString,'零拾','零')
Set @UpString = REPLACE(@UpString,'零佰','零')
Set @UpString = REPLACE(@UpString,'零仟','零')
Set @UpString = REPLACE(@UpString,'零零零','零')
Set @UpString = REPLACE(@UpString,'零零','零')
Set @UpString = REPLACE(@UpString,'零角零分','整')
Set @UpString = REPLACE(@UpString,'零分','整')
Set @UpString = REPLACE(@UpString,'零角','零')

Set @UpString = REPLACE(@UpString,'零亿零万零元','亿元')
Set @UpString = REPLACE(@UpString,'亿零万零元','亿元')
Set @UpString = REPLACE(@UpString,'零亿零万','亿')
Set @UpString = REPLACE(@UpString,'零万零元','万元')
Set @UpString = REPLACE(@UpString,'万零元','万元')
Set @UpString = REPLACE(@UpString,'零亿','亿')
Set @UpString = REPLACE(@UpString,'零万','万')
Set @UpString = REPLACE(@UpString,'零元','元')
Set @UpString = REPLACE(@UpString,'零零','零')


If ( '元' = substring(@UpString,1,1))
    Set @UpString = substring(@UpString,2,(len(@UpString) - 1))

If ( '零' = substring(@UpString,1,1))
    Set @UpString = substring(@UpString,2,(len(@UpString) - 1))


If ( '角' = substring(@UpString,1,1))
    Set @UpString = substring(@UpString,2,(len(@UpString) - 1))

If ( '分' = substring(@UpString,1,1))
    Set @UpString = substring(@UpString,2,(len(@UpString) - 1))

If ( '整' = substring(@UpString,1,1))
    Set @UpString = '零元整'


Return  @UpString

END


阅读更多
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭