T-SQL自定义函数:小写金额转大写金额
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
ALTER FUNCTION gFun_ConvertToUpperCase
(
@LowerCase DECIMAL(20,2)
)
RETURNS NVARCHAR(50)
AS
/*
Author Date Comments
---------- ---------- ----------
yanl 2008-12-26 Create
Function:
--将小写数字的金额改成大写中文金额
In:
--@LowerCase:小写金额
Out:
--大写金额
Remarks:
select dbo.gFun_ConvertToUpperCase(RAND()*10000000)
*/
BEGIN
--DELETE FROM @Upper
DECLARE @Upper TABLE(ID SMALLINT, CN NVARCHAR(2))
INSERT INTO @Upper([ID],[CN])
SELECT 0, N'零'
UNION
SELECT 1, N'壹'
UNION
SELECT 2, N'贰'
UNION
SELECT 3, N'叁'
UNION
SELECT 4, N'肆'
UNION
SELECT 5, N'伍'
UNION
SELECT 6, N'陆'
UNION
SELECT 7, N'柒'
UNION
SELECT 8, N'捌'
UNION
SELECT 9, N'玖'
UNION
SELECT 10, N'拾'
--drop table #Unit
--delete from #Unit
DECLARE @Unit TABLE(ID SMALLINT,CN NVARCHAR(10),Change1 NVARCHAR(10),Change2 NVARCHAR(10))
INSERT INTO @Unit ([ID],[CN],[Change1],[Change2])
SELECT 0, N'分', N'零分', NULL
UNION
SELECT 1, N'角', N'零角', NULL
UNION
SELECT 2, N'圆', N'零圆', N'圆'
UNION
SELECT 3, N'拾', N'零拾', NULL
UNION
SELECT 4, N'佰', N'零佰', NULL
UNION
SELECT 5, N'仟', N'零仟', NULL
UNION
SELECT 6, N'万', N'零万', N'万'
UNION
SELECT 7, N'拾', N'零拾', N'零'
UNION
SELECT 8, N'佰', N'零佰', N'零'
UNION
SELECT 9, N'仟', N'零仟', N'零'
UNION
SELECT 10, N'亿', N'零亿', NULL
DECLARE @xMoney INT
DECLARE @Row SMALLINT
SET @xMoney = CONVERT(INT, @LowerCase*100)
SET @Row = 0
--drop table #Money
DECLARE @Money TABLE (ID SMALLINT, Number INT, UpperCase NVARCHAR(10))
WHILE (@xMoney <> 0)
BEGIN
INSERT INTO @Money ([ID], [Number])
SELECT @Row,@xMoney
SET @xMoney = @xMoney / 10
SET @Row = @Row + 1
END
UPDATE a
SET a.[Number] = CONVERT(INT,RIGHT(CONVERT(VARCHAR(20), a.[Number]),1))
FROM @Money a
UPDATE a
SET a.[UpperCase] = b.[CN] + c.[CN]
--SELECT b.[CN] + c.[CN]
FROM @Money a
LEFT JOIN @Upper b ON a.[Number] = b.[ID]
LEFT JOIN @Unit c ON a.[ID] = c.[ID]
UPDATE a
SET a.[UpperCase] = b.[Change2]
--SELECT *
FROM @Money a
INNER JOIN @Unit b ON a.[ID] = b.[ID] AND a.[UpperCase] = b.[Change1]
DECLARE @UpperCase NVARCHAR(50)
SET @UpperCase = ''
SELECT @UpperCase = @UpperCase + [UpperCase]
FROM @Money a
WHERE a.UpperCase IS NOT NULL
ORDER BY [ID] DESC
RETURN @UpperCase
END
GO