将阿拉伯数字(1234)钱化为中国数字钱(壹贰叁肆)

最新推荐文章于 2021-09-08 22:30:36 发布

jnu2003

最新推荐文章于 2021-09-08 22:30:36 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： insert list function table null

本文链接：https://blog.youkuaiyun.com/jnu2003/article/details/6816870

SQL 专栏收录该内容

10 篇文章

订阅专栏

创建了一个名为[dbo].[GetChineseAmount]的SQL函数，该函数将输入的货币金额（如1234）转换为对应的汉字表示，例如壹贰叁肆。函数首先进行参数检查，然后将数字拆分为整数和小数部分，并分别转换。整数部分通过一个包含汉字数字的列表进行转换，小数部分则相应地转换为'分'或'角'。最后，合并两部分并返回结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE FUNCTION [dbo].[GetChineseAmount] (@Money money)
	RETURNS varchar(255)
AS   
begin 
	DECLARE @Source varchar(255), @Temp nvarchar(2), @Unit nchar(1)
	DECLARE @Segment1 varchar(255), @Segment2 varchar(255), @Result1 nvarchar(255), @Result2 nvarchar(255)
	DECLARE @Length tinyint, @i tinyint, @x tinyint
	DECLARE @List table(ID int identity(1,1), Item nchar(1))
  
	-- 参数检查
	IF @Money IS NULL
		RETURN NULL
  
	IF @Money = 0
		RETURN '零'
  
	-- 填充表
	INSERT INTO @List (Item) VALUES ('壹')
	INSERT INTO @List (Item) VALUES ('贰')
	INSERT INTO @List (Item) VALUES ('叁')
	INSERT INTO @List (Item) VALUES ('肆')
	INSERT INTO @List (Item) VALUES ('伍')
	INSERT INTO @List (Item) VALUES ('陆')
	INSERT INTO @List (Item) VALUES ('柒')
	INSERT INTO @List (Item) VALUES ('捌')
	INSERT INTO @List (Item) VALUES ('玖')
  
	-- 分为整数和小数两段
	SELECT @Source = CONVERT(varchar(255), @Money, 0)
	SELECT @i = PATINDEX('%.%', @Source)
	SELECT @Segment1 = LEFT(@Source, @i - 1), @Segment2 = RIGHT(@Source, 2)
	SELECT @Length = LEN(@Segment1)
  
	IF @Length > 12
	begin
		SELECT @Result1 = 'CONVERT OVER!'
		RETURN @Result1
	end
  
	-- 转换整数部分
	SELECT @i = 1, @Result1 = '', @Temp = '', @x = 0
	WHILE @i <= @Length
	begin
		SELECT @Temp = SUBSTRING(@Segment1, (@Length - @i) + 1, 1)

		-- 数字
		SELECT @x = CAST(@Temp AS tinyint)
		IF @x = 0
			SELECT @Temp = '零'
		else
			SELECT @Temp = Item FROM @List WHERE (ID = @x)

		-- 单位
		IF @Temp != '零'
		begin
			SELECT @Unit =
			CASE
			WHEN (@i = 1) THEN '元'
			WHEN (@i = 2) OR (@i = 6) OR (@i = 10) THEN '拾'
			WHEN (@i = 3) OR (@i = 7) OR (@i = 11) THEN '佰'
			WHEN (@i = 4) OR (@i = 8) OR (@i = 12) THEN '仟'
			WHEN (@i = 5) THEN '万'
			WHEN (@i = 9) THEN '亿'
			WHEN ((@i > 5) AND (@i < 9) AND (CHARINDEX(@Result1, '万') = 0)) THEN '万'
			WHEN ((@i > 9) AND (CHARINDEX(@Result1, '亿') = 0)) THEN '亿'
			end
			SELECT @Temp = (@Temp + @Unit)
		end else begin
			IF (@i = 1) OR (@i = 5)
			begin
				if (@i = 1)
					SELECT @Temp = '元'
				if (@i = 5)
					SELECT @Temp = '万'
			end 
			else  
			begin
			--edit by vincent 
			/*
				01 一个0
				00 无0
				11 无0
				10 无0
			*/
				if  (
					 (
					convert(int,SUBSTRING(@Segment1, (@Length - @i+1), 1)) > 0
					or (SUBSTRING(@Segment1, (@Length - @i+1), 1) = '0'
					and (SUBSTRING(@Segment1, (@Length - @i+2), 1) = '0'))
					)
					or LEFT(@Result1, 2) = '元'
					)
						select @Temp=''
			
--				IF (SUBSTRING(@Segment1, (@Length - @i), 1) = '0') OR (LEFT(@Result1, 2) = '元')
--					SELECT @Temp = ''
			end
		end

		SELECT @Result1 = (@Temp + @Result1), @i = (@i + 1) 
	end 
  
	-- 转换小数部分
	SELECT @i = 1, @Result2 = '', @Temp = '', @x = 0 
	WHILE @i <= 2 
	begin
		SELECT @Temp = SUBSTRING(@Segment2, (2 - @i) + 1, 1)
  
		-- 数字
		SELECT @x = CAST(@Temp AS tinyint)
		IF @x = 0
			SELECT @Temp = '零'
		else
			SELECT @Temp = Item FROM @List WHERE (ID = @x)
  
		-- 单位
		IF @Temp != '零'
		begin
			SELECT @Temp =
			CASE @i
			WHEN 1 THEN (@Temp + '分')
			WHEN 2 THEN (@Temp + '角')
			end
		end else begin
			SELECT @Temp =
			CASE
			WHEN (@i = 1) THEN ''
			WHEN (@i = 2) AND (@Result2 != '') THEN '零'
			WHEN (@i = 2) AND (@Result2 = '') THEN ''
			end
		end
		SELECT @Result2 = @Temp + @Result2, @i = (@i + 1)
	end

	-- 合并两部分
	SELECT @Length = LEN(@Result1), @i = LEN(@Result2)

	IF (@Length = 1)
		SELECT @Result1 = ''

--	IF (@Length > 1) AND (@i = 0)
--		SELECT @Result2 = '整'

--	RETURN @Result1 + @Result2
	RETURN @Result1 + @Result2 + '正'
end