金额小写转大写 sql函数
1.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--功能： 用于将小写的数值翻译成大写的字符串（支持到分，即小数点后两位）
--入口参数：@decNum------数字型变量
--返回：字符串
--举例：select dbo.fn_ChnMoney(623.88)
-- 结果为“陆佰贰拾叁元捌角捌分”
ALTER FUNCTION [dbo].[fn_ChnMoney_New](@decNum decimal(18,2))
RETURNS varchar(200)
AS
BEGIN
DECLARE @chvNum varchar(200)
DECLARE @chvMoney varchar(200)
DECLARE @chvTemp varchar(200)
DECLARE @intIntLen int
DECLARE @intI int
DECLARE @chvTempI varchar(200)
DECLARE @chvReturn varchar(200)
DECLARE @sFsFlag int
SET @sFsFlag = 0
IF @decNum=0
SET @chvMoney = '零'
Else
BEGIN
IF @decNum<0
BEGIN
SET @sFsFlag = 1
SET @decNum = ABS(@decNum)
END
SET @chvTemp=convert(varchar(200),Round(@decNum*100,0))
IF charindex('.',@chvTemp,1)>0
SET @chvNum=left(@chvTemp,charindex('.',@chvTemp,1)-1)
ELSE
SET @chvNum=@chvTemp
SET @intIntLen=len(@chvNum)
SET @chvMoney=''
Set @chvReturn = ''
SET @intI=1
WHILE @intI <= @intIntLen
BEGIN
SET @chvTempI = substring(@chvNum,@intIntLen-@intI+1,1)
SET @chvMoney = substring('零壹贰叁肆伍陆柒捌玖',convert(int,@chvTempI)+1,1) + substring('分角元拾佰仟万拾佰仟亿拾佰仟兆拾佰仟京拾佰仟',(@intI-1)+1,1)
SET @intI = @intI + 1
SET @chvReturn = @chvMoney + @chvReturn
END
END
SET @chvReturn=Replace(@chvReturn,'零仟','零')
SET @chvReturn=Replace(@chvReturn,'零佰','零')
SET @chvReturn=Replace(@chvReturn,'零拾','零')
while charindex('零零',@chvReturn,1)>0
SET @chvReturn=Replace(@chvReturn,'零零','零')
SET @chvReturn=Replace(@chvReturn,'零兆','兆')
SET @chvReturn=Replace(@chvReturn,'零亿','亿')
SET @chvReturn=Replace(@chvReturn,'零万','万')
SET @chvReturn=Replace(@chvReturn,'零元','元')
SET @chvReturn=Replace(@chvReturn,'零角零分','整')
SET @chvReturn=Replace(@chvReturn,'零角','零')
SET @chvReturn=Replace(@chvReturn,'零分','整')
SET @chvReturn=LTRIM(RTRIM(@chvReturn))
IF @sFsFlag = 1
SET @chvReturn = '负' + Ltrim(@chvReturn)
RETURN @chvReturn
END
2.数字转换（不带 元角分 ）
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--功能： 用于将小写的数值翻译成大写的字符串（支持到分，即小数点后两位）
--入口参数：@decNum------数字型变量
--返回：字符串
--举例：select dbo.fn_ChnMoney(623.88)
-- 结果为“陆佰贰拾叁元捌角捌分”
ALTER FUNCTION [dbo].[fn_ChnMoney](@decNum decimal(18,2))
RETURNS varchar(200)
AS
BEGIN
DECLARE @chvNum varchar(200)
DECLARE @chvMoney varchar(200)
DECLARE @chvTemp varchar(200)
DECLARE @intIntLen int
DECLARE @intI int
DECLARE @chvTempI varchar(200)
DECLARE @chvReturn varchar(200)
IF @decNum=0
SET @chvMoney = '零'
Else
BEGIN
SET @chvTemp=convert(varchar(200),Round(@decNum*100,0))
IF charindex('.',@chvTemp,1)>0
字符串函数中将大写转换为小写 SET @chvNum=left(@chvTemp,charindex('.',@chvTemp,1)-1)
ELSE
SET @chvNum=@chvTemp
SET @intIntLen=len(@chvNum)
SET @chvMoney=''
Set @chvReturn = ''
SET @intI=1
WHILE @intI <= @intIntLen
BEGIN
SET @chvTempI = substring(@chvNum,@intIntLen-@intI+1,1)
SET @chvMoney = substring('零壹贰叁肆伍陆柒捌玖',convert(int,@chvTempI)+1,1) + substring('分角元拾佰仟万拾佰仟亿拾佰仟兆拾佰仟京拾佰仟',(@intI-1)+1,1)
SET @intI = @intI + 1
SET @chvReturn = @chvMoney + @chvReturn
END
END
SET @chvReturn=Replace(@chvReturn,'零仟','零')
SET @chvReturn=Replace(@chvReturn,'零佰','零')
SET @chvReturn=Replace(@chvReturn,'零拾','零')
while charindex('零零',@chvReturn,1)>0
SET @chvReturn=Replace(@chvReturn,'零零','零')
SET @chvReturn=Replace(@chvReturn,'零兆','兆')
SET @chvReturn=Replace(@chvReturn,'零亿','亿')
SET @chvReturn=Replace(@chvReturn,'零万','万')
SET @chvReturn=Replace(@chvReturn,'零元','元')
SET @chvReturn=Replace(@chvReturn,'零角零分','整')
SET @chvReturn=Replace(@chvReturn,'零角','零')
SET @chvReturn=Replace(@chvReturn,'零分','整')
SET @chvReturn=LTRIM(RTRIM(@chvReturn))
RETURN @chvReturn
END
Excel vba 函数，数字转化成大写人民币
Function DXRMB(M)
'函数名为DXRMB
y = Int(Round(100 * Abs(M)) / 100)
j = Round(100 * Abs(M) + 0.00001) - y * 100
f = (j / 10 - Int(j / 10)) * 10
a = IIf(y < 1, "", Application.Text(y, "[DBNum2]") & "元")
b = IIf(j > 9.5, Application.Text(Int(j / 10), "[DBNum2]") & "角", IIf(y < 1, "", IIf(f > 1, "零", "")))
c = IIf(f < 1, "整", Application.Text(Round(f, 0), "[DBNum2]") & "分")
DXRMB = IIf(Abs(M) < 0.005, "", IIf(M < 0, "负" & a & b & c, a & b & c))
End Function
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论