求大佬給個SqlServer數字金額轉英文大寫的函式
uj5u.com熱心網友回復：
CREATE FUNCTION [dbo].[NumToChn] (@num NUMERIC(19,6))
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @n_data NVARCHAR(20),@c_data NVARCHAR(100),@n_str NVARCHAR(10),@i INT
SET @n_data=https://bbs.csdn.net/topics/RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS BIGINT) AS NVARCHAR(20)),14)
SET @c_data=https://bbs.csdn.net/topics/ ''
SET @i=1 WHILE @i <=14
BEGIN
SET @n_str=SUBSTRING(@n_data,@i,1)
IF @n_str <> ''
BEGIN IF NOT ((SUBSTRING(@n_data,@i,2)= '00') OR ((@n_str= '0') AND ((@i=4) OR (@i=8) OR (@i=12) OR (@i=14))))
SET @c_data=https://bbs.csdn.net/topics/@c_data+SUBSTRING( N'零壹貳叁肆伍陸柒捌玖',CAST(@n_str AS INT)+1,1)
IF NOT ((@n_str= '0') AND (@i <> 4) AND (@i <> 8) AND (@i <> 12))
SET @c_data=https://bbs.csdn.net/topics/@c_data+SUBSTRING( N'仟佰拾億仟佰拾萬仟佰拾圓角分',@i,1)
IF SUBSTRING(@c_data,LEN(@c_data)-1,2)= N'億萬'
SET @c_data=https://bbs.csdn.net/topics/SUBSTRING(@c_data,1,LEN(@c_data)-1) END
SET @i=@i+1 END IF @num <0
SET @c_data=https://bbs.csdn.net/topics/ '（負數）'+@c_data
IF @num=0 SET @c_data=https://bbs.csdn.net/topics/ '零圓'
IF @n_str= '0'
SET @c_data=https://bbs.csdn.net/topics/@c_data+ '整'
RETURN(@c_data)
END
uj5u.com熱心網友回復：
CREATE FUNCTION [dbo].[Num2eng] (@num numeric(15,2))
RETURNS varchar(400) WITH ENCRYPTION
AS
BEGIN
--All rights reserved. pbsql
DECLARE @i int,@hundreds int,@tenth int,@one int
DECLARE @thousand int,@million int,@billion int
DECLARE @numbers varchar(400),@s varchar(15),@result varchar(400)
SET @numbers='one two three four five '
+'six seven eight nine ten '
+'eleven twelve thirteen fourteen fifteen '
+'sixteen seventeen eighteen nineteen '
+'twenty thirty forty fifty '
+'sixty seventy eighty ninety '
SET @s=RIGHT('000000000000000'+CAST(@num AS varchar(15)),15)
SET @billion=CAST(SUBSTRING(@s,1,3) AS int)--將12位整數分成4段：十億、百萬、千、百十個
SET @million=CAST(SUBSTRING(@s,4,3) AS int)
SET @thousand=CAST(SUBSTRING(@s,7,3) AS int)
SET @result=''
SET @i=0
WHILE @i<=3
BEGIN
SET @hundreds=CAST(SUBSTRING(@s,@i*3+1,1) AS int)--百位0-9
SET @tenth=CAST(SUBSTRING(@s,@i*3+2,1) AS int)
SET @one=(CASE @tenth WHEN 1 THEN 10 ELSE 0 END)+CAST(SUBSTRING(@s,@i*3+3,1) AS int)--個位0-19
SET @tenth=(CASE WHEN @tenth<=1 THEN 0 ELSE @tenth END)--十位0、2-9
IF (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0)) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0)) or
(@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0))
SET @result=@result+', '--百位不是0則每段之間加連接符,
IF (@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds=0 and (@tenth>0 or @one>0)))
SET @result=@result+' and '--百位是0則加連接符AND
IF @hundreds>0
SET @result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+' hundred'
IF @tenth>=2 and @tenth<=9
BEGIN
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+171,10))
END
IF @one>=1 and @one<=19
BEGIN
IF @tenth>0
SET @result=@result+'-'
ELSE
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@one*10-9,10))
END
IF @i=0 and @billion>0
SET @result=@result+' billion'
IF @i=1 and @million>0
SET @result=@result+' million'
IF @i=2 and @thousand>0
SET @result=@result+' thousand'
SET @i=@i+1
END
IF SUBSTRING(@s,14,2)<>'00'
BEGIN
SET @result=@result+' point '
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'zero'
ELSE
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1) AS int)*10-9,10))
IF SUBSTRING(@s,15,1)<>'0'
SET @result=@result+' '+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1) AS int)*10-9,10))
END
RETURN(@result)
END
uj5u.com熱心網友回復：
這個前面是對的 就是整數的時候末尾少了only
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/124708.html
標籤：疑難問題
上一篇：求
下一篇：2000資料庫未能撤消日志記錄