---
# System prepended metadata

title: 數字轉中文金額
tags: [ORACLE, SQL, MSSQL]

---

# 數字轉中文金額
###### tags: `SQL` `ORACLE` `MSSQL`

# mssql
```sql=
USE [LCMS]
GO
/****** Object:  UserDefinedFunction [dbo].[f_chinese_amt]    Script Date: 2020/6/3 上午 10:19:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[f_chinese_amt] (@num INT) 
returns VARCHAR(100) 
AS 
  BEGIN 
      DECLARE @temp INT, 
              @res  INT, 
              @i    TINYINT 
      DECLARE @str  VARCHAR(100), 
              @no   VARCHAR(20), 
              @unit VARCHAR(16) 

      SELECT @str = '', 
             @no = '零壹貳參肆伍陸柒捌玖', 
             @unit = '拾佰仟萬拾佰仟億' 

      SET @temp=@num 

      SELECT @i = 0, 
             @res = @temp%10, 
             @temp = @temp / 10 

      WHILE @temp > 0 
        BEGIN 
            IF @i = 0 
              SET @str=Substring(@no, @res + 1, 1) 
            ELSE 
              SET @str=Substring(@no, @res+1, 1) 
                       + Substring(@unit, @i, 1) + @str 

            SELECT @res = @temp%10, 
                   @temp = @temp / 10 

            SET @i=@i + 1 
        END 

      SET @str=Substring(@no, @res+1, 1) 
               + Substring(@unit, @i, 1) + @str 
      SET @str=Replace(@str, '零拾', '零') 
      SET @str=Replace(@str, '零佰', '零') 
      SET @str=Replace(@str, '零仟', '零') 
      SET @str=Replace(@str, '零拾', '零') 
      SET @str=Replace(@str, '零萬', '萬') 

      WHILE @i > 0 
        BEGIN 
            SET @str=Replace(@str, '零', '零') 
            SET @i=Charindex('零零', @str) 
        END 

      SET @str=Replace(@str, '零萬', '萬') 
      SET @str=Replace(@str, '億萬', '億') 

      IF RIGHT(@str, 1) = '零' 
        SET @str=LEFT(@str, Len(@str) - 1) 

      SET @str=@str + '元整' 

      RETURN @str 
  END 
```

# oralce 
```sql=
    FUNCTION f_chinese_amt(form_amt NUMBER) RETURN VARCHAR2 IS

    w_amt        VARCHAR2(12) := 0;
    w_length    NUMBER(12) := 0;
    w_temp        VARCHAR2(50) := NULL;
    w_format    VARCHAR2(100) := NULL;
    w_count        NUMBER(12) := 0;
    w_minus_flag    VARCHAR2(1);
    w_form_amt    NUMBER;

BEGIN

    IF form_amt < 0 THEN
        w_form_amt := form_amt * -1;
        w_minus_flag := 'Y';
    ELSE
        w_form_amt := form_amt;
        w_minus_flag := 'N';
    END IF;
    
    w_count := LENGTH(w_form_amt);

    w_length := 12;

    w_amt := LPAD(TO_CHAR(w_form_amt),12,'0');


    --個位數-------------------------------------------------------|

    IF SUBSTR(w_amt,w_length-3,1) = 0 THEN

        IF SUBSTR(w_amt,w_length-2,1) = 0 THEN

            IF SUBSTR(w_amt,w_length-1,1) = 0 THEN
                IF SUBSTR(w_amt,w_length,1) = 0 THEN
                    w_format := w_format;
                    --w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                    --w_format := w_temp||w_format;
                ELSE
                    IF w_count > 4 THEN
                        w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                        w_format := w_temp||w_format;
                        w_temp := f_trans_code( SUBSTR(w_amt,w_length-3,1) );
                        w_format := w_temp||w_format;
                    ELSE
                        w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                        w_format := w_temp||w_format;
                    END IF;

                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-1,1) );
                w_format := w_temp||'拾'||w_format;
            END IF;
        ELSE
            IF SUBSTR(w_amt,w_length-1,1) = 0 THEN
                IF SUBSTR(w_amt,w_length,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                    w_format := w_temp||w_format;
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-1,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-1,1) );
                w_format := w_temp||'拾'||w_format;
            END IF;

            w_temp := f_trans_code( SUBSTR(w_amt,w_length-2,1) );
            w_format := w_temp||'佰'||w_format;
        END IF;


    ELSIF SUBSTR(w_amt,w_length-3,1) <> 0 THEN

        IF SUBSTR(w_amt,w_length-2,1) = 0 THEN

            IF SUBSTR(w_amt,w_length-1,1) = 0 THEN
                IF SUBSTR(w_amt,w_length,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                    w_format := w_temp||w_format;
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-1,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-1,1) );
                w_format := w_temp||'拾'||w_format;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-2,1) );
                w_format := w_temp||w_format;

            END IF;
        ELSE
            IF SUBSTR(w_amt,w_length-1,1) = 0 THEN
                IF SUBSTR(w_amt,w_length,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                    w_format := w_temp||w_format;
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-1,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-1,1) );
                w_format := w_temp||'拾'||w_format;
            END IF;

            w_temp := f_trans_code( SUBSTR(w_amt,w_length-2,1) );
            w_format := w_temp||'佰'||w_format;
        END IF;

        w_temp := f_trans_code( SUBSTR(w_amt,w_length-3,1) );
        w_format := w_temp||'仟'||w_format;

    END IF;

    ---------------------------------------------------------|


    --萬位數-------------------------------------------------------|

    IF w_count > 4 THEN

        IF SUBSTR(w_amt,w_length-7,1) <> 0
            OR SUBSTR(w_amt,w_length-6,1) <> 0
            OR SUBSTR(w_amt,w_length-5,1) <> 0 
            OR SUBSTR(w_amt,w_length-4,1) <> 0 THEN

            w_format := '萬'||w_format;

        END IF;    

    IF SUBSTR(w_amt,w_length-7,1) = 0 THEN

        IF SUBSTR(w_amt,w_length-6,1) = 0 THEN

            IF SUBSTR(w_amt,w_length-5,1) = 0 THEN
                IF SUBSTR(w_amt,w_length-4,1) = 0 THEN
                    w_format := w_format;
                    --w_temp := f_trans_code( SUBSTR(w_amt,w_length-4,1) );
                    --w_format := w_temp||w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-4,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length-4,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-4,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-5,1) );
                w_format := w_temp||'拾'||w_format;
            END IF;
        ELSE
            IF SUBSTR(w_amt,w_length-5,1) = 0 THEN
                IF SUBSTR(w_amt,w_length-4,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-4,1) );
                    w_format := w_temp||w_format;
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-5,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length-4,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-4,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-5,1) );
                w_format := w_temp||'拾'||w_format;
            END IF;

            w_temp := f_trans_code( SUBSTR(w_amt,w_length-6,1) );
            w_format := w_temp||'佰'||w_format;
        END IF;


    ELSIF SUBSTR(w_amt,w_length-7,1) <> 0 THEN

        IF SUBSTR(w_amt,w_length-6,1) = 0 THEN

            IF SUBSTR(w_amt,w_length-5,1) = 0 THEN
                IF SUBSTR(w_amt,w_length-4,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-4,1) );
                    w_format := w_temp||w_format;
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-5,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length-4,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-4,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-5,1) );
                w_format := w_temp||'拾'||w_format;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-6,1) );
                w_format := w_temp||w_format;

            END IF;
        ELSE
            IF SUBSTR(w_amt,w_length-5,1) = 0 THEN
                IF SUBSTR(w_amt,w_length-4,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-4,1) );
                    w_format := w_temp||w_format;
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-5,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length-4,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-4,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-5,1) );
                w_format := w_temp||'拾'||w_format;
            END IF;

            w_temp := f_trans_code( SUBSTR(w_amt,w_length-6,1) );
            w_format := w_temp||'佰'||w_format;
        END IF;

        w_temp := f_trans_code( SUBSTR(w_amt,w_length-7,1) );
        w_format := w_temp||'仟'||w_format;

    END IF;


    END IF;

    ---------------------------------------------------------|

    --億位數-------------------------------------------------------|

    IF w_count > 8 THEN

    w_format := '億'||w_format;

    IF SUBSTR(w_amt,w_length-11,1) = 0 THEN

        IF SUBSTR(w_amt,w_length-10,1) = 0 THEN

            IF SUBSTR(w_amt,w_length-9,1) = 0 THEN
                IF SUBSTR(w_amt,w_length-8,1) = 0 THEN
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-8,1) );
                    w_format := w_temp||w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-8,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length-8,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-8,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-9,1) );
                w_format := w_temp||'拾'||w_format;
            END IF;
        ELSE
            IF SUBSTR(w_amt,w_length-9,1) = 0 THEN
                IF SUBSTR(w_amt,w_length-8,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-8,1) );
                    w_format := w_temp||w_format;
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-9,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length-8,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-8,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-9,1) );
                w_format := w_temp||'拾'||w_format;
            END IF;

            w_temp := f_trans_code( SUBSTR(w_amt,w_length-10,1) );
            w_format := w_temp||'佰'||w_format;
        END IF;


    ELSIF SUBSTR(w_amt,w_length-11,1) <> 0 THEN

        IF SUBSTR(w_amt,w_length-10,1) = 0 THEN

            IF SUBSTR(w_amt,w_length-9,1) = 0 THEN
                IF SUBSTR(w_amt,w_length-8,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-8,1) );
                    w_format := w_temp||w_format;
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-9,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length-8,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-8,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-9,1) );
                w_format := w_temp||'拾'||w_format;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-10,1) );
                w_format := w_temp||w_format;

            END IF;
        ELSE
            IF SUBSTR(w_amt,w_length-9,1) = 0 THEN
                IF SUBSTR(w_amt,w_length-8,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-8,1) );
                    w_format := w_temp||w_format;
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-9,1) );
                    w_format := w_temp||w_format;
                END IF;
            ELSE
                IF SUBSTR(w_amt,w_length-8,1) = 0 THEN
                    w_format := w_format;
                ELSE
                    w_temp := f_trans_code( SUBSTR(w_amt,w_length-8,1) );
                    w_format := w_temp||w_format;
                END IF;
                w_temp := f_trans_code( SUBSTR(w_amt,w_length-9,1) );
                w_format := w_temp||'拾'||w_format;
            END IF;

            w_temp := f_trans_code( SUBSTR(w_amt,w_length-10,1) );
            w_format := w_temp||'佰'||w_format;
        END IF;

        w_temp := f_trans_code( SUBSTR(w_amt,w_length-11,1) );
        w_format := w_temp||'仟'||w_format;

    END IF;


    END IF;

    ---------------------------------------------------------|


    IF w_minus_flag = 'Y' THEN
        w_format := '負 '||w_format||'元整';
    ELSE
        w_format := w_format||'元整';
    END IF;
    
    IF NVL(form_amt, 0) = 0 THEN
        w_format := '零元整';
    END IF;

    RETURN w_format;

END f_chinese_amt;  

```