数字转英文的函数
转载请注明出处,联系我:田工,15118249062(微信同号)
create FUNCTION dbo.FounctionNumToEnglish (@num NUMERIC(15, 2))
RETURNS VARCHAR(400)
AS
BEGIN
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 + ' '--百位是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 + ' '
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 + ' '
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 + ' AND '
SET @result = @result + REPLACE([dbo].[FounctionNumToEnglish](CAST(SUBSTRING(@s, 14, 2) AS INT)), 'ONLY', ' CENTS ')
END
SET @RESULT = UPPER(@RESULT) + ' ONLY'
RETURN (@result)
END
GO