个人网站建设yxhuying网推

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

1.函数转换

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=0WHILE @i<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
selecta.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