# 數字轉中文金額
###### 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;
```
Sign in with Wallet
Connect another wallet