做微商网站需要哪些seo对网店推广的作用
需求：将金额转换成中文大写格式填入单据合计行： _佰_拾_万_仟_佰_拾_元_角_分
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