[T-SQL] 自訂函數練習: 整數金額轉換國字大寫

在資料庫中建立自訂函數，將輸入的金額轉換成國字大寫格式。

網路版本 (轉貼自：https://daimom3020.blogspot.com/2017/10/sql.html)

create function moneytochinese (@money numeric(14,2))
returns nvarchar(32) as
begin
    declare @money_num nvarchar(20)    --存儲金額的字符形式
    declare @money_chn nvarchar(32)    --存儲金額的中文大寫形式
    declare @n_chn nvarchar(1)  --這個的作用是什麼???
    declare @i int    --臨時變量
 
    select @money_chn=case when @money>=0 then ' ' else null end
    select @money=abs(@money)  --abs()取絕對值
    select @money_num=stuff(str(@money, 15, 2), 13, 1, ' ')    --加前置空格補齊到位（去掉小數點）[把金額全部變成數字串]
    select @i=patindex('%[1-9]%', @money_num)    --找到金額最高位
 
    while @i>=1 and @i<=14 --假定金額的最小與最高位數
    begin
        set @n_chn=substring(@money_num, @i, 1) --

        if @n_chn<>'0' or (substring(@money_num,@i+1,1)<>'0' and @i not in(4, 8, 12, 14))  --條件判斷該數字是否為國字數字/或需要轉換 [4-->千, 8-->千萬,...]
            set @money_chn=@money_chn+substring('零壹貳參肆伍陸柒捌玖', @n_chn+1, 1) --[轉換阿拉伯數字為中文大寫形式] [substring篩選出當下的數字對應到第幾個字] 
        if @n_chn<>'0' or @i in(4, 8, 12)    --添加中文單位
            set @money_chn=@money_chn+substring('仟佰拾億仟佰拾萬仟佰拾圓角分',@i,1)     
   
        set @i=@i+1
    end
 
    set @money_chn=replace(@money_chn, '億萬', '億')    --當金額為x億零萬時去掉萬
    if @money=0 set @money_chn='零圓整'     --當金額為零時返回‘零圓整‘
    if @n_chn='0' set @money_chn=@money_chn+'整'    --當金額末尾為零分時以‘整‘結尾
 
    return @money_chn    --返回大寫金額
end

老師解答版本

drop function if exists int2chinese

go

create function int2chinese(@v bigint)
returns varchar(50)
as
begin
 declare @org varchar(50) = cast(@v as varchar(50))
 declare @unit varchar(50) = '元拾佰仟'
 declare @mapping varchar(50) = '零壹貳參肆伍陸柒捌玖'
 declare @idx int = 0
 declare @idx1 int = 0
 declare @chinese varchar(50) = ''
 declare @tmp varchar(10)
 declare @total int = len(@org) / 4

 set @org = '00000000000000000' + @org
 set @org = right(@org, 16)


 set @tmp = left(@org, 4)

 while @tmp <> '' begin

  while @idx < len(@tmp) begin
   if substring(@tmp, @idx + 1, 1) <> '0' begin
    set @chinese = @chinese + 
       substring(@mapping, cast(substring(@tmp, @idx + 1, 1) as int) + 1, 1)
        + substring(@unit, len(@tmp) - @idx, 1)
   end else begin  
    if right(@chinese, 1) <> '零' begin
     set @chinese = @chinese + '零'
    end
   end

   set @idx = @idx + 1
  end

  if @total = 3 begin
   if @idx1 = 0 begin
    set @chinese = iif(right(@chinese, 1) in ('元', '零'), left(@chinese, len(@chinese) - 1) + '兆', @chinese)
   end

   if @idx1 = 1 begin
    set @chinese = iif(right(@chinese, 1) in ('元', '零'), left(@chinese, len(@chinese) - 1) + '億', @chinese)
   end
  
   if @idx1 = 2 begin
    set @chinese = iif(right(@chinese, 1) in ('元', '零'), left(@chinese, len(@chinese) - 1) + '萬', @chinese)
   end
  end

  if @total = 2 begin
   if @idx1 = 1 begin
    set @chinese = iif(right(@chinese, 1) in ('元', '零'), left(@chinese, len(@chinese) - 1) + '億', @chinese)
   end
  
   if @idx1 = 2 begin
    set @chinese = iif(right(@chinese, 1) in ('元', '零'), left(@chinese, len(@chinese) - 1) + '萬', @chinese)
   end
  end

  if @total = 1 begin
   if @idx1 = 2 begin
    set @chinese = iif(right(@chinese, 1) in ('元', '零'), left(@chinese, len(@chinese) - 1) + '萬', @chinese)
   end
  end

  set @idx = 0
  set @idx1 = @idx1 + 1
  set @org = right(@org, len(@org) - 4)
  set @tmp = left(@org, 4)
 end

 if left(@chinese, 1) = '零' begin
  set @chinese = substring(@chinese, 2, len(@chinese) - 1)
 end

 return @chinese + '整'
end

go 

select dbo.int2chinese(1000010004)

練習成果

--把數字金額轉換成中文大寫的函數
--1034換成壹仟零佰參拾肆元整

Create Function moneytochinese(@money int)
Returns nvarchar(50)
AS
 BEGIN
  Declare @output nvarchar(50)   --最後轉換成的中文字串
  Declare @money_char nvarchar(50)  --將整數型態的金額轉換成文字型態
  Declare @temp_char nvarchar(10)  --將選中的那個欄位文字提取出來
  Declare @lenth int   --字串的長度
  Declare @i int = 1 --迴圈變數

  select @output=case when @money>=0 then ' ' else null end  --設定output預設為空白
  select @money_char = cast(@money as nvarchar(50))          --轉成文字
  select @money_char = str(@money_char,12,0)                 --直接給到最大位數12位(前面系統自動補空格)
  select @lenth= len(@money_char)                            --確定字串長度
  select @i = patindex('%[1-9]%', @money_char)               --抓數字在第幾位開始
  
  while @i <= @lenth
   Begin
    select @temp_char = substring(@money_char,@i,1)   --抓出金額字

    If @temp_char != '0' or (substring(@money_char,@i+1,1)) != '0' and @i not in (4, 8, 12)  --抓出中文數字 確定後一位不為零且不在億萬圓上
     set @output = @output + substring('零壹貳參肆伍陸柒捌玖',@temp_char+1,1)
     
    If @temp_char != '0' or @i in (4, 8, 12)    --插入中文單位 (4-億, 8-萬, 12-圓)
     set @output = @output + substring('仟佰拾億仟佰拾萬仟佰拾圓',@i,1)

    set @i = @i + 1
   End

  If @money = 0 set @output = '零圓整'else set @output = @output+'整'
  set @output = replace(@output, '億萬', '萬')
  

  Return @output
 END



 select dbo.moneytochinese(256489)  --呼叫函數