SQL 数字金额转为英文形式
摘录自 http://blog.sina.com.cn/s/blog_43ad83920100nkfp.html
1 IF EXISTS ( SELECT * 2 FROM SYS.OBJECTS 3 WHERE NAME LIKE 'F_NUM_ENG' 4 AND TYPE = 'FN' ) 5 DROP FUNCTION F_NUM_ENG 6 GO 7 CREATE FUNCTION [DBO].[F_NUM_ENG] ( @NUM NUMERIC(15, 2) ) 8 RETURNS VARCHAR(400) 9 AS 10 BEGIN 11 DECLARE @I INT , 12 @HUNDREDS INT , 13 @TENTH INT , 14 @ONE INT 15 DECLARE @THOUSAND INT , 16 @MILLION INT , 17 @BILLION INT 18 DECLARE @NUMBERS VARCHAR(400) , 19 @S VARCHAR(15) , 20 @RESULT VARCHAR(400) 21 SET @NUMBERS = 'ONE TWO THREE FOUR FIVE ' 22 + 'SIX SEVEN EIGHT NINE TEN ' 23 + 'ELEVEN TWELVE THIRTEEN FOURTEEN FIFTEEN ' 24 + 'SIXTEEN SEVENTEEN EIGHTEEN NINETEEN ' 25 + 'TWENTY THIRTY FORTY FIFTY ' 26 + 'SIXTY SEVENTY EIGHTY NINETY ' 27 SET @S = RIGHT('000000000000000' + CAST(@NUM AS VARCHAR(15)), 15) 28 SET @BILLION = CAST(SUBSTRING(@S, 1, 3) AS INT)--将12位整数分成4段：十亿、百万、千、百十个 29 SET @MILLION = CAST(SUBSTRING(@S, 4, 3) AS INT) 30 SET @THOUSAND = CAST(SUBSTRING(@S, 7, 3) AS INT) 31 SET @RESULT = '' 32 SET @I = 0 33 WHILE @I <= 3 34 BEGIN 35 SET @HUNDREDS = CAST(SUBSTRING(@S, @I * 3 + 1, 1) AS INT)--百位0-9 36 SET @TENTH = CAST(SUBSTRING(@S, @I * 3 + 2, 1) AS INT) 37 SET @ONE = ( CASE @TENTH 38 WHEN 1 THEN 10 39 ELSE 0 40 END ) + CAST(SUBSTRING(@S, @I * 3 + 3, 1) AS INT)--个位0-19 41 SET @TENTH = ( CASE WHEN @TENTH <= 1 THEN 0 42 ELSE @TENTH 43 END )--十位0、2-9 44 IF ( @I = 1 45 AND @BILLION > 0 46 AND ( @MILLION > 0 47 OR @THOUSAND > 0 48 OR @HUNDREDS > 0 49 ) 50 ) 51 OR ( @I = 2 52 AND ( @BILLION > 0 53 OR @MILLION > 0 54 ) 55 AND ( @THOUSAND > 0 56 OR @HUNDREDS > 0 57 ) 58 ) 59 OR ( @I = 3 60 AND ( @BILLION > 0 61 OR @MILLION > 0 62 OR @THOUSAND > 0 63 ) 64 AND ( @HUNDREDS > 0 ) 65 ) 66 SET @RESULT = @RESULT + ' '--百位不是0则每段之间加连接符, 67 IF ( @I = 3 68 AND ( @BILLION > 0 69 OR @MILLION > 0 70 OR @THOUSAND > 0 71 ) 72 AND ( @HUNDREDS = 0 73 AND ( @TENTH > 0 74 OR @ONE > 0 75 ) 76 ) 77 ) 78 SET @RESULT = @RESULT + ' '--百位是0则加连接符AND 79 IF @HUNDREDS > 0 80 SET @RESULT = @RESULT + RTRIM(SUBSTRING(@NUMBERS, 81 @HUNDREDS * 10 - 9, 82 10)) + ' HUNDRED' 83 IF @TENTH >= 2 84 AND @TENTH <= 9 85 BEGIN 86 IF @HUNDREDS > 0 87 --SET @RESULT=@RESULT+' AND ' 88 SET @RESULT = @RESULT + ' ' 89 SET @RESULT = @RESULT + RTRIM(SUBSTRING(@NUMBERS, 90 @TENTH * 10 91 + 171, 10)) 92 END 93 IF @ONE >= 1 94 AND @ONE <= 19 95 BEGIN 96 IF @TENTH > 0 97 SET @RESULT = @RESULT + '-' 98 ELSE 99 IF @HUNDREDS > 0 100 SET @RESULT = @RESULT + ' ' 101 SET @RESULT = @RESULT + RTRIM(SUBSTRING(@NUMBERS, 102 @ONE * 10 - 9, 103 10)) 104 END 105 IF @I = 0 106 AND @BILLION > 0 107 SET @RESULT = @RESULT + ' BILLION' 108 IF @I = 1 109 AND @MILLION > 0 110 SET @RESULT = @RESULT + ' MILLION' 111 IF @I = 2 112 AND @THOUSAND > 0 113 SET @RESULT = @RESULT + ' THOUSAND' 114 SET @I = @I + 1 115 END 116 IF SUBSTRING(@S, 14, 2) <> '00' 117 BEGIN 118 SET @RESULT = @RESULT + ' AND ' 119 120 SET @RESULT = @RESULT 121 + REPLACE([DBO].[F_NUM_ENG](CAST(SUBSTRING(@S, 14, 2) AS INT)), 122 'ONLY', ' CENTS ') 123 END 124 SET @RESULT = UPPER(@RESULT) + ' ONLY' 125 RETURN(@RESULT) 126 END 127 GO 128 SELECT [DBO].[F_NUM_ENG](804.00) 129 SELECT [DBO].[F_NUM_ENG](804.54)
