sql server 数字转人民币大写 

 1 alter FUNCTION dbo.NumToRMB(@num numeric(14, 5))
 2     RETURNS varchar(100)
 3     WITH ENCRYPTION
 4 AS
 5 BEGIN
 6     DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int
 7     SET @n_data = RIGHT(SPACE(14) + CAST(CAST(ABS(@num * 100) AS bigint) AS varchar(20)), 14)
 8     SET @c_data = ''
 9     SET @i = 1
10     WHILE @i <= 14
11         BEGIN
12             SET @n_str = SUBSTRING(@n_data, @i, 1)
13             IF @n_str <> ' '
14                 BEGIN
15                     IF not ((SUBSTRING(@n_data, @i, 2) = '00') or
16                             ((@n_str = '0') and ((@i = 4) or (@i = 8) or (@i = 12) or (@i = 14))))
17                         SET @c_data = @c_data + SUBSTRING('零壹贰叁肆伍陆柒捌玖', CAST(@n_str AS int) + 1, 1)
18                     IF not ((@n_str = '0') and (@i <> 4) and (@i <> 8) and (@i <> 12))
19                         SET @c_data = @c_data + SUBSTRING('仟佰拾亿仟佰拾万仟佰拾圆角分', @i, 1)
20                     IF SUBSTRING(@c_data, LEN(@c_data) - 1, 2) = '亿万'
21                         SET @c_data = SUBSTRING(@c_data, 1, LEN(@c_data) - 1)
22                 END
23             SET @i = @i + 1
24         END
25     IF @num < 0
26         SET @c_data = '（负数）' + @c_data
27     IF @num = 0
28         SET @c_data = '零圆'
29     IF @n_str = '0'
30         SET @c_data = @c_data + '整'
31     RETURN (@c_data)
32 END
33 
34 -- SELECT dbo.NumToRMB(1200)
View Code

 

posted @ 2023-02-11 14:41  iHey  阅读(58)  评论(0)    收藏  举报