SQL 金额数值转换成中文大写

本文介绍: 需求：将金额转换成中文大写格式填入单据合计行： _佰_拾_万_仟_佰_拾_元_角_分。测试：将a mount列的值转换为中文大写。2.在查询条件中直接转换。

需求：将金额转换成中文大写格式填入单据合计行： _佰_拾_万_仟_佰_拾_元_角_分
1234567.89
壹佰贰拾叁万肆仟伍佰陆拾柒元捌角玖分

drop function n2C;

CREATE FUNCTION n2C (@num numeric(14,2))    
RETURNS VARCHAR(20)    
AS    
BEGIN    
    DECLARE @result VARCHAR(20) = '',@I INT;  
	SET @num=@num*100  
	SET @i=0
    WHILE @i&lt;9   
    BEGIN    
        SET @result = SUBSTRING('零壹贰叁肆伍陆柒捌玖', @num%10 + 1, 1) + @result;    
        SET @num = @num / 10;    
		SET @i=@i+1;
    END    
    RETURN  @result;   
END

测试：将amount列的值转换为中文大写

SELECT 
a.amount as amount,
dbo.n2C(a.amount) AS number2Chinese,
LEFT(dbo.n2C(a.amount), 1) AS 佰,
SUBSTRING(dbo.n2C(a.amount), 2, 1)  AS 拾,
SUBSTRING(dbo.n2C(a.amount), 3, 1) AS 万,
SUBSTRING(dbo.n2C(a.amount), 4, 1) AS 仟,
SUBSTRING(dbo.n2C(a.amount), 5, 1) AS 佰,
SUBSTRING(dbo.n2C(a.amount), 6, 1) AS 拾,
SUBSTRING(dbo.n2C(a.amount), 7, 1) AS 元,
SUBSTRING(dbo.n2C(a.amount), 8, 1) AS 角,
RIGHT(dbo.n2C(a.amount), 1)AS 分
from a

2.在查询条件中直接转换

use Test
select
	a.amount as amount,
	SUBSTRING('零壹贰叁肆伍陆柒捌玖', (convert(numeric(19, 4) , a.amount , 1)* 100)/ 100000000%10 + 1, 1) as 佰万,
	SUBSTRING('零壹贰叁肆伍陆柒捌玖',(convert(numeric(19, 4) , a.amount , 1)* 100)/ 10000000%10 + 1, 1) as 拾万,
	SUBSTRING('零壹贰叁肆伍陆柒捌玖', (convert(numeric(19, 4) , a.amount , 1)* 100)/ 1000000%10 + 1, 1) as 万,
	SUBSTRING('零壹贰叁肆伍陆柒捌玖',(convert(numeric(19, 4) , a.amount , 1)* 100)/ 100000%10 + 1, 1) as 仟,
	SUBSTRING('零壹贰叁肆伍陆柒捌玖', (convert(numeric(19, 4) , a.amount , 1)* 100)/ 10000%10 + 1, 1) as 佰,
	SUBSTRING('零壹贰叁肆伍陆柒捌玖', (convert(numeric(19, 4) , a.amount , 1)* 100)/ 1000%10 + 1, 1) as 拾,
	SUBSTRING('零壹贰叁肆伍陆柒捌玖', (convert(numeric(19, 4) , a.amount , 1)* 100)/ 100%10 + 1, 1) as 元,
	SUBSTRING('零壹贰叁肆伍陆柒捌玖', (convert(numeric(19, 4) , a.amount , 1)* 100)/ 10%10 + 1, 1) as 角,
	SUBSTRING('零壹贰叁肆伍陆柒捌玖', (convert(numeric(19, 4) , a.amount , 1)* 100)%10 + 1, 1) as 分
from a