当前位置：首页 > news >正文

网站规划与建设与安全管理品牌软文案例

news 2026/4/6 23:21:09

网站规划与建设与安全管理,品牌软文案例,jsp网站制作详细教程,学习网站网址大全需求：将金额转换成中文大写格式填入单据合计行： _佰_拾_万_仟_佰_拾_元_角_分 1234567.89 壹佰贰拾叁万肆仟伍佰陆拾柒元捌角玖分 1.函数转换 drop function n2C;CREATE FUNCTION n2C (num numeric(14,2)) RETURNS VARCHAR(20) AS BEGIN …

需求：将金额转换成中文大写格式填入单据合计行： _佰_拾_万_仟_佰_拾_元_角_分
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