关闭

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

标签: 数据库sql serverfunction
622人阅读 评论(0) 收藏 举报
分类:

利用函数实现,以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


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:30446次
    • 积分:565
    • 等级:
    • 排名:千里之外
    • 原创:20篇
    • 转载:3篇
    • 译文:0篇
    • 评论:8条
    最新评论